How do I create a formula to count the weeks past the date field

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

We have a date field that represents the date a project officially kicks off. I want QB to "count" the weeks as time progresses past that date. 

So if the date in the field is Feb 29, 2016, I want the "count" field to change to "1" on Mar 7, and then to change to "2" on Mar 14, etc etc.

Is this possible?

Photo of Jennifer

Jennifer

  • 20 Points

Posted 3 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 62,520 Points 50k badge 2x thumb
Try this (not tested)



var date EndDate = IF(IsNull([Project End Date]), [Project End Date], Today());

INT((Todays($EndDate - [Project Kick off date]))+1)/7)

That should give the integer portion of the # of weeks between Today (or if the project is ended, the project end date) and the project kick off date.
Photo of Jennifer

Jennifer

  • 20 Points
For this, do I use a formula date field?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,520 Points 50k badge 2x thumb
A formula numeric field.
Photo of Jennifer

Jennifer

  • 20 Points
Progress but not there yet. The formula does not work if the end date is null. And when the end date is not null, the calculation is incorrect. For one entry in particular, the formula is showing 56, when it should be 18. Let me know if there is a way to communicate directly. Thanks!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,520 Points 50k badge 2x thumb
My contact information is in my profile, but I do try to help people on this forum when I have time in between my work for clients.

Sorry, I had that first formula backwards

var date EndDate = IF(not IsNull([Project End Date]), [Project End Date], Today());

INT((Todays($EndDate - [Project Kick off date]))+1)/7)

Can you give me an example of the dates you are using and the result, if the above fix does not work.
Photo of Jennifer

Jennifer

  • 20 Points
Success! The updated formula works!
Photo of Jennifer

Jennifer

  • 20 Points
Thank you!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,520 Points 50k badge 2x thumb
Thx for letting know.