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.
 3,152 Points
Posted 2 years ago
QuickBaseCoach App Dev./Training, Champion
 65,664 Points
Try this
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.
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.
 3,152 Points
Thanks a lot. A great solution. I checked the output by comparing with Excel calculation, and vast majority of the result is consistent. Would this be any different or more precise if we use work week? Also when I see an negative number, does that signify the week number of project start in future?
QuickBaseCoach App Dev./Training, Champion
 65,664 Points
In the negative number issue, you are right. That should be week zero. Here is a fix for that.
Max(0,
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.
Max(0,
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.
 3,152 Points
Thanks a lot. Will email an excel file just to compare projects which are under way.
QuickBaseCoach App Dev./Training, Champion
 65,664 Points
Can you try this formula and let me know if there is still a problem. I was missing a bracket in my original post snd you may have added it in in the wrong place to fix the syntax error.
Max(0,
Ceil(ToDays(($TodayOrProjectEndDate  [Project Start Date]))/7))
Max(0,
Ceil(ToDays(($TodayOrProjectEndDate  [Project Start Date]))/7))
 3,152 Points
Thanks. Yes, you are right I thought there was an additional bracket so I removed it. I have now put it back. Many thanks for clearing it up.
Related Categories

Forms
 3277 Conversations
 134 Followers

Relationships
 2628 Conversations
 47 Followers

Roles & permissions
 2244 Conversations
 23 Followers

Tables & fields
 7217 Conversations
 182 Followers