Skip to main content

I am using the field, "Total Days Open" to count the days between the date something is submitted and the date that work is completed. The formula is: 

IF(AND({Work Completed} = BLANK(), {Submission Date} = BLANK()), "",
IF({Work Completed} = BLANK(), DATETIME_DIFF(TODAY(), {Submission Date}, "d"), DATETIME_DIFF({Work Completed}, {Submission Date}, "d")))

On a separate tab, I want to calculate the average total of days based on the Health Plan link to the records I want to average. 

I have been trying to use the Rollup field but it is returning a value of "NaN"

Hm, any chance you could share an example base with this set up so that we could help you debug it?

Eyeballing your formula, I would suggest trying to make sure that the "IF()"s always output a number, and so instead of outputting "", output 0 instead

Hm, any chance you could share an example base with this set up so that we could help you debug it?

Eyeballing your formula, I would suggest trying to make sure that the "IF()"s always output a number, and so instead of outputting "", output 0 instead


Sorry for the late reply here! I was actually able to figure this one out. 

Your suggestion above was correct to have the formula output 0. So the new formula is: 

IF(AND({Work Completed} = BLANK(), {Submitted} = BLANK()),0,
IF({Work Completed} = BLANK(), DATETIME_DIFF(TODAY(), {Submitted}, "d"), DATETIME_DIFF({Work Completed}, {Submitted}, "d")))
 
On the Rollup field, I updated the formula to:
IF(OR(AVERAGE(values), AVERAGE(values)=0), AVERAGE(values))
 
The Rollup is now returning the correct averaged values of the date difference. 

 

 

Reply


Terms & Conditions