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
• Question
• Updated 4 years ago

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.

• 0 Points

Posted 4 years ago

• 0

QuickBasePros_IDS, Champion

• 3,770 Points
Try this:

If([Participant Type]="Residential" and

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

null)
• 0 Points
This always displays a blank.

QuickBasePros_IDS, Champion

• 3,770 Points
Is your formula a Formula-Duration field type?
• 0 Points
Yes

QuickBasePros_IDS, Champion

• 3,770 Points
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.
• 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)
• 0 Points
I had to post as an answer in order to attach a screen shot.

QuickBasePros_IDS, Champion

• 3,770 Points
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.