Can anyone help me with a formula to calculate the number of days someone has spent on a project with relation to today's date?

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

I'm looking for something to calculate the following -

If today is between [Participant Start Date] and [Participant End Date] then count the number of days from [Participant Start Date] through today, otherwise show 0.

I'd also like to surround the formula above with another if statement such that if the [Participant Type] is "Residential" the above should executed, and if not then do nothing.

Thanks in advance.

Photo of Hands

Hands

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 3,770 Points 3k badge 2x thumb
Try this:

If([Participant Type]="Residential" and

Today() >= [Participant Start Date] and Today() <= [Participant End Date], Today()-[Participant Start],

null)
Photo of Hands

Hands

  • 0 Points
This always displays a blank.
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 3,770 Points 3k badge 2x thumb
Is your formula a Formula-Duration field type?
Photo of Hands

Hands

  • 0 Points
Yes
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 3,770 Points 3k badge 2x thumb
Can you post on here your formula, and demonstrate in a screenshot the portion of the record which is not delivering the value that the formula is expecting (like I did in the answer above).  If not, please contact me offline so we can try to solve the problem.
Photo of Hands

Hands

  • 0 Points
I used the following formula and created a report to show Participant Start Date, Participant End Date and Participant Days On Project So Far, which is where the formula is. A screen shot of the report is attached.



If([Participant Type]="Residential" and

Today() >= [Participant Start Date] and Today() <= [Participant End Date], Today()-[Participant Start Date],

null)
Photo of Hands

Hands

  • 0 Points
I had to post as an answer in order to attach a screen shot.
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 3,770 Points 3k badge 2x thumb
Your screenshot does not show whether the record is a Residential participant type; so we expect the value to be Null for some of the records which do not meet the criteria.  Also, all the records you did display were for 2014, so the formula would ignore those records since Today() is not within 2014.