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.
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.
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).
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:
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
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.
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
