In one table (Table A) I’m using a Datetime_Diff function to calculate the difference (duration) between two date columns.
Each record of Table A is linked to a date in Table B (there can be multiple records in Table A with the same date in Table B).
In Table B, I’m trying to use the Rollup function to “sum” the durations calculated in Table A so that I can see a “daily duration total”. This returns a NaN value no matter how I try and calculate/add midsteps.
Only work around is manually entering Table A’s duration as a duration field.
Example here is Equipment downtime.
Table A has the “time the equipment went down” and the “time the equipment returned to service”. This table also calculates the duration of time for each piece of equipment.
Table B is a daily summary, and I’d like to be able to Sum the downtime for all equipment.
Thanks in advance!
Welcome to Airtable Community !
Believe this is a Formatting issue. When you do the Rollup, check Formatting tab and make sure you are using the correct formatting.
What you have described should work.
Welcome to the community, @Devon_Chisholm! :grinning_face_with_big_eyes: You say you’re using a
DATETIME_DIFF() function to calculate the date differences. Could you please share the full formula for that field? Something tells me we’ll find a clue in there that explains what’s going wrong with the rollup.
“NaN” is short for “Not a Number”, meaning that the values you’re attempting to add via the SUM() function are not numbers. Are you adding other text to the numerical result from
DATETIME_DIFF()? If so, that changes the field from a number to a string, and strings can’t be added mathematically, even if there are numbers in them.
Thanks for the quick responses - looks like it was a case of some afternoon fatigue.
Correct solution is indeed a “Downtime” column with > Datetime_DIff (time down, time up) and then rolling that column up in the other table via “sum” and format: Duration.
I have a weird feeling I was accidentally trying to roll up another column called “TD” which was a datetime_format column to just display the hour/minute down.
Thanks again !