How can I do time/hours arithmetic in Google Spreadsheet?

Question

How do I do time/hour arithmetic in a Google spreadsheet?

I have a value that is time (e.g., 36:00:00) and I want to divide it by another time (e.g., 3:00:00) and get 12. If I divide just one by the other, I get 288:00:00 when what I want is 12 (or 12:00:00).

Note that using the hours() function doesn't work, because 36:00:00 becomes 12.

Sirius

=A1*24 converts "1:30:00" into "1.5" – Hoxha

Maybe you can also follow this solution (https://mcmap.net/q/160221/-how-to-convert-duration-of-mm-ss-to-seconds-in-google-sheets). It helped me a lot. – Milagro

Answer 1

When the number being returned by your formula is being formatted as a time, and you want it formatted as a plain number, change the format of the cell to a plain number format: click the cell and then click Format, Number, Normal.

Time values in Google spreadsheet are represented as days and parts of days. For example, 36:00:00 is the formatted representation of the number 1.5 (a day and a half).

Suppose you divide 36:00:00 by 3:00:00, as in your example. Google Spreadsheet performs the calculation 1.5 divided by 0.125, which is 12. The result tells you that you have 12 3-hour intervals in a 36-hour time period. 12, of course, is not a time interval. It is a unitless quantity.

Going the other way, it is possible to format any number as a time. If you format 12 as a time, it's reasonable to expect that you will get 288:00:00. 12 days contain 288 hours.

Golliner

Brilliant answer. Do you know if it is possible to do Format/Number/Normal within a formula (e.g., =Format.Number.Normal(A3))? Without that, I need to create a duplicate column of the hour column with the normal formatting. – Sirius

Actually, the Format for me was "duration" so the times got sum up as a single total – Pyrophoric

Answer 2

Google Sheets now have a duration formatting option. Select: Format -> Number -> Duration.

Brei

Casebash and @xanderoid. Great! Now how to format as a duration without seconds. Chances are people are using this because of timesheets. Any addition that I can add to make a duration show in HH:mm? I tried TEXT formula to format, but that doesn't do durations. – Thus

You need to do a custom date format for just elapsed seconds. – Erigeron

Answer 3

Example of calculating time:

work-start   work-stop   lunchbreak    effective time

  07:30:00    17:00:00          1.5                 8  [=((A2-A1)*24)-A3]

If you subtract one time value from another the result you get will represent the fraction of 24 hours, so if you multiply the result with 24 you get the value represented in hours.

In other words: the operation is mutiply, but the meaning is to change the format of the number (from days to hours).

Atronna

And how to sum up effective time rows? – Barbarize

@Atronna I think you meant to show this formula: =((B2-A2)*24)-C2 but even the modified formula does not work in the current version of GSheets when using the Duration format. In order to get decimal hours you need to do something like this: abs(-time(8,30,0)-1)*24 = 8.5 – Tympanum

Answer 4

You can use the function TIME(h,m,s) of google spreadsheet. If you want to add times to each other (or other arithmetic operations), you can specify either a cell, or a call to TIME, for each input of the formula.

For example:

• B3 = 10:45
• C3 = 20 (minutes)
• D3 = 15 (minutes)
• E3 = 8 (hours)
• F3 = B3+time(E3,C3+D3,0) equals 19:20

Outgrow

Answer 5

I used the TO_PURE_NUMBER() function and it worked.

Bischoff

This is a lot easier than the conversion to hours from hours:min:sec that I was doing previously – Pistil

This seems to be the best way to go. Especially when summing more than a day of hours. Note that the result is in decimal days, so if you're looking for hours you need to multiply by 24. – Chorizo

Answer 6

I had a similar issue and i just fixed it for now

1. format each of the cell to time
2. format the total cell (sum of all the time) to Duration

Consciousness

Answer 7

So much simpler: look at this

B2: 23:00
C2:  1:37
D2: = C2 - B2 + ( B2 > C2 )

Why it works, time is a fraction of a day, the comparison B2>C2 returns True (1) or False (0), if true 1 day (24 hours) is added. http://www.excelforum.com/excel-general/471757-calculating-time-difference-over-midnight.html

Ondrea

Answer 8

if you have duration in h:mm, the actual value stored in that cell is the time converted to a real number, divided by 24 hours per day.

ex: 6:45 or 6 hours 45 minutes is 6.75 hours 6.75 hours / 24 = 0.28125 (in other words 6hrs45minutes is 28.125% of a day). If you use a column to convert your durations into actual numbers (in example, converting 6:45 into 0.28125) then you can do you multiplication or division and get the correct answer.

Calling

Answer 9

In the case you want to format it within a formula (for example, if you are concatenating strings and values), the aforementioned format option of Google is not available, but you can use the TEXT formula:

=TEXT(B1-C1,"HH:MM:SS")

Therefore, for the questioned example, with concatenation:

="The number of " & TEXT(B1,"HH") & " hour slots in " & TEXT(C1,"HH") _
& " is " & TEXT(C1/B1,"HH")

Cheers

Janinajanine

Answer 10

In an fresh spreadsheet with 36:00:00 entered in A1 and 3:00:00 entered in B1 then:

=A1/B1

say in C1 returns 12.

Tale

Answer 11

Type the values in single cells, because google spreadsheet cant handle duration formats at all, in any way shape or form. Or you have to learn to make scripts and graduate as a chopper pilot. that is also a option.

Hughie