-1

As show in this image:

data

I have those 2 cells with DATETIME and I need to do the delta of those in order to get Day - HH:mm:ss. How can I do that? If i try to use DATETIME_DIFF I get error. Any tip?

What i wanna get:

result

6
  • 1
    Welcome. Would you please edit your question and show in your snapshot, how a successful calculation would be displayed. – Tedinoz Apr 2 at 13:28
  • 1
    FWIW, DATETIME_DIFF is not a function available to Google Sheets. The reference you quoted is for "Data Studio". – Tedinoz Apr 2 at 13:29
  • @Tedinoz added a screen of the result I need! I need to get x Days y hours: z mins: k secs – user269271 Apr 7 at 10:56
  • Thanks for that. How about a harder one, like 16/03/2021 10:13:00 -> 22/03/2021 08:52:00. What's your expected answer for that? – Tedinoz Apr 9 at 9:02
  • @Tedinoz This should be 6 days Xh:Ym:Zs (the time that goes from 10:13:00 to 08:52:00, e.g. 6days 17h:mm:ss) – user269271 Apr 12 at 11:04
0

To get the difference between the datetime values in cells AA4 and Z4, use =AA4 - Z4. The result will be a dateserial value. Format it as Format > Number > Duration to make it pretty. To do the same for the whole column, put this formula in row 4 of a free column:

=arrayformula( 
  if( 
    isnumber(AA4:AA) * isnumber(Z4:Z), 
    AA4:AA - Z4:Z, 
    iferror(1/0) 
  ) 
)

Here is a quick — but a verbose — explanation of how date and time values work in spreadsheets. Hope it helps you understand what is going on.

Google Sheets datetimes and durations are stored as numbers that correspond to the number of days since the start of "the Epoch", i.e., 30 December 1899. The fractional part corresponds to the fraction of the day. When a cell is formatted as a date, time or duration, the number is simply shown in years, months, days, hours, minutes and/or seconds, but its underlying value remains unchanged.

In other words, all date, datetime, time and duration values are just numbers formatted as such — the date 2020-04-17 is 43938, the datetime 2020-04-17 12:00 noon is 43938.5, the time 12:00 noon is 0.5, and the duration 06:00 is 0.25. The value 1 corresponds to one day, i.e., 24 hours.

Since the datetime value tells the number of days, you can increment a date by one day simply by adding 1, as in =A2 + 1. You can compare two dates the same way you would compare two numbers, as in =A2 < B2. You can add a duration to a date or a datetime to get a datetime, and subtract one datetime from another to get a duration.

One hour is 1/24 of a day, so to multiply an hourly rate such as $30/h by a duration, use =(24 * R2) * D2 where cell R2 contains the hourly rate and cell D2 contains the duration.

One minute is 1/24/60 of a day, so to multiply a revolutions per minute number such as 3000 rpm by a duration to get the total number of revolutions, use =(24 * 60 * R2) * D2.

One second is 1/24/60/60 of a day, so to multiply a speed given in meters per second such as 10 m/s by a duration to get a distance in meters, use =(24 * 60 * 60 * R2) * D2.

Note how these formulas do not convert the time value. It remains a dateserial value that expresses a fraction of the day. The value that is converted is the rate — it is multiplied to get rate per day from rate per hour, rate per minute or rate per second.

I find it easiest to always work with time and date using the built-in datetime number formats because that way you never have trouble with time values that exceed 24 hours, date values that exceed 365 days, and do not need to take leap years and such into account — all those details are automatically taken care of by the spreadsheet's built-in functionality if you just stick to formatting all date and time cells as dates and times, and use regular arithmetic to add and subtract them.

One often sees formulas that multiply hours by 24 to convert time values into "digital hours" where 10.50 does not mean ten hours 50 minutes but ten and a half hours. This sometimes causes misunderstandings so I prefer using the built-in time formats that express the same as 10:30. The latter value can be directly added to a date, while the former cannot.

Not the answer you're looking for? Browse other questions tagged or ask your own question.