I need to work out the week number between two days so that the week is numbered as 12, 3, 4 etc. So as an example if my site start date is 1 Nov 17 and the project completion date is 31 Dec 17, there are 8.5 weeks in between. So if the project is in the first week, I want this to say week 1 and in the second week, week 2 and so on. Tried some options but does not quite work the way I want this to be. Will appreciate some help.
var date TodayOrProjectEndDate = Min(Today(),[Project End date]);
Ceil(ToDays(($TodayOrProjectEndDate - [Project Start Date])/7)
The formula variable should return the lesser of Today and the Project End date. That way if you look back at an old project the clock will have stopped at the Project End Date and not "Today" which may be 2 years later.
The Ceil will round up, so that a project which is 1 day old = 0.14 Weeks old, will round up to 1.
Ceil(ToDays(($TodayOrProjectEndDate - [Project Start Date])/7) )
If you have an example where the results don't agree with excel can you give me the access Camille and the results which don't match snd which you feel is correct.