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

• 0
• Question
• Updated 3 years ago

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?

• 20 Points

Posted 3 years ago

• 0

QuickBaseCoach App Dev./Training, Champion

• 69,674 Points
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.
• 20 Points
For this, do I use a formula date field?

QuickBaseCoach App Dev./Training, Champion

• 69,674 Points
A formula numeric field.
• 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!

QuickBaseCoach App Dev./Training, Champion

• 69,674 Points
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.
• 20 Points
Success! The updated formula works!
• 20 Points
Thank you!

QuickBaseCoach App Dev./Training, Champion

• 69,674 Points
Thx for letting know.