Skip to main content

I have a doozy of a formula problem and would LOVE some help.

 

My base has a DATE field called {RR Actual Start Date} and a DATE field called {RR Complete Date}. Date format for both fields is Local (M/DD/YYYY). Default is off, Include time is off, Display time zone is off.

I created a FORMULA field called {RR Time to Complete} in which I calculate the difference between the {RR Actual Start Date} and the {RR Complete Date}. All worked fine (and I’ll explain more below) until I tried to create a Rollup to average the values in the {RR Time to Complete} column and kept getting a NaN error no matter what.

Here’s the detail:

{RR Time to Complete} in holds this formula:

IF(OR({RR Actual Start Date}=BLANK(), {RR Complete Date}=BLANK()), 0, IF({Raw_RR_Diff}<1, 1, {Raw_RR_Diff})). The output is formatted as Number with no presets and zero decimal places.

The {Raw_RR_Diff} field is a FORMULA column that contains DATETIME_DIFF({RR Complete Date}, {RR Actual Start Date}, ‘days’). It is also formatted as Number with no presets and zero decimal places.

 

Where the {RR Time to Complete} field returns 0 when either (or both) of {RR Actual Start Date} and {RR Complete Date} are blank, the {Raw_RR_Diff} field, the {Raw_RR_Diff} field returns NaN. This is expected, because the Raw_RR_Diff column deliberately removed the error handling to try to find the source of the NaN on the rollup. 

 

(Note that there is no way to avoid blanks in the {RR Actual Start Date} or {RR Complete Date} columns, as those are not populated until the related activity starts or ends, so some way to handle blank cells is needed.)

 

I then created a FORMULA field called {Debug_Main_toString} that contains this formula:

“”&{RR Time to Complete}

This is checking for any random, stray characters, which I can confirm by comparing {RR Time to Complete}, {Raw_RR_Diff} and {Debug_Main_toString} outputs. The outputs for {Debug_Main_toString} are identical to {RR Time to Complete}. This is expected.

 

I then created a FORMULA field called {Debug_Main_Value} that contains this formula:

VALUE({RR Time to Complete}).

This is (or should be) converting the output from {RR Time to Complete} to a number. The output for all values (whether number or NaN) is #ERROR

 

Lastly, I created a FORMULA field called {Debug_Main_IsError_Value} that contains this formula:

IF(ISERROR(VALUE({RR Time to Complete})), “Not a Number”, “Is a Number”)

The outputs for this field are “Not a Number” no matter what the output is for {RR Time to Complete}.

 

I am at a loss for how to get Airtable to recognize the difference between the two dates as a number, and the issue does not seem specific to the blank cells, or zero, because Airtable is not recognizing ANY output in the {RR Time to Complete} field as a number.

 

Structure for reference. Note NaN result on line 2.

 

 

 

 

 

 

Hi,
You have error because you are trying to convert Number to Number. VALUE requires String.
Btw, “The outputs for {Debug_Main_toString} are identical to {RR Time to Complete}.” - not exactly,  strings are left aligned.
I hope this helps you. 
Check Average of the whole  {RR Time to Complete} column in the bottom totals line.
Your rollup should work, check if you are have correct field in Rollup settings. Maybe you put wrong column there.
 


Note you can put 0 or you can put nothing, both works, but nothing ignored when count average
I’ve shorten formula.

 



THIS IS ANOTHER WAY, PUT NOTHING IN OUTPUT where date(s) absent . I’m not sure which is correct for you
 

 

Reply


Terms & Conditions