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.
This question is related to
time
google-sheets
datetime-format
date-arithmetic
I used the TO_PURE_NUMBER()
function and it worked.
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
.
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
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:
Google Sheets now have a duration formatting option. Select: Format -> Number -> Duration.
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.
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
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.
I had a similar issue and i just fixed it for now
Source: Stackoverflow.com