Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Need help with NaN when using formula to track difference in dates

Topic Labels: Formulas
428 2
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi, having trouble getting rid of the “NaN” when trying to figure out the average difference in dates per sales manager.

In one table (named “Tracking Log)” have a date field for “Transport Order Date” and a date field for “Commitment Date”. I used the following formula to look determine the difference in dates by number of days for each sale they made in a field I named “Time to Order Transport (days)”.

IF(
AND(
{Transport Order Date},
{Commitment Date}
),DATETIME_DIFF({Transport Order Date},{Commitment Date},‘days’),BLANK())

Each manager is doing dozens of these transactions each month so I now want to see the Average days per manager for this date difference. I have another table (named “Sales Manager”) where I did a look-up of the field from Tracking log named “Time to Order Transport (days)”. I then created a new a rollup field, as well as a formula field using “Average”, however I have some new managers who have just started and have blank fields and I can’t figure out how to ignore those blanks in the average fields. For those listed managers it just states NaN, which then doesn’t allow me to see the average of the whole office. Any advice appreciated

2 Replies 2

Hi,

Why don’t you set condition in rollup field? (Only include linked records from the Tracking Log table that meet certain conditions)

@Jason_Cohen As a side note, you can omit the BLANK() function from the end of the formula that you wrote above. If the third argument in an IF() function is omitted it automatically leaves the field empty.