Skip to main content

Rollup Calculated Time Difference

  • March 19, 2020
  • 3 replies
  • 49 views
D
Forum|alt.badge.img+7

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

Mohamed_Swella1
Forum|alt.badge.img+17

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

Justin_Barrett
Forum|alt.badge.img+21

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.

D
Forum|alt.badge.img+7
  • Devon_Chisholm
  • Author
  • New Participant
  • March 20, 2020

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 !

Terms & ConditionsAccessibility statement