Week Number Between Two Dates

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered

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.  

Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,664 Points 50k badge 2x thumb
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.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
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?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,664 Points 50k badge 2x thumb
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.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks a lot.  Will email an excel file just to compare projects which are under way.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,664 Points 50k badge 2x thumb
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))
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
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.