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.
 0 Points
Posted 4 years ago
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)
If([Participant Type]="Residential" and
Today() >= [Participant Start Date] and Today() <= [Participant End Date], Today()[Participant Start],
null)
QuickBasePros_IDS, Champion
 3,770 Points
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)
If([Participant Type]="Residential" and
Today() >= [Participant Start Date] and Today() <= [Participant End Date], Today()[Participant Start Date],
null)
 0 Points
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.
Related Categories

Formulas & functions
 2927 Conversations
 75 Followers

Tables & fields
 7210 Conversations
 182 Followers