Help

Rollup Calculated Time Difference

Topic Labels: Formulas
1242 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Devon_Chisholm
4 - Data Explorer
4 - Data Explorer

Hi,

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!

3 Replies 3

Hi @Devon_Chisholm

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.

BR,
Mo

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.

Devon_Chisholm
4 - Data Explorer
4 - Data Explorer

Hi all!

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 !