How can I do time/hours arithmetic in Google Spreadsheet?
Solution 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.
Solution 2:
Google Sheets now have a duration formatting option. Select: Format -> Number -> Duration.
Solution 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).
Solution 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
Solution 5:
I had a similar issue and i just fixed it for now
- format each of the cell to time
- format the total cell (sum of all the time) to Duration