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

User · Answer

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

User · Answer

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

User · Answer

I used the TO PURE NUMBER   function and it worked

User · Answer

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

User · Answer

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

User · Answer

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

User · 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    amp  TEXT B1  HH    amp    hour slots in    amp  TEXT C1  HH      amp    is    amp  TEXT C1 B1  HH     Cheers

User · Answer

So much simpler  look at this  B2  23 00 C2   1 37 D2    C2 - B2     B2  gt  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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to time

Examples related to google-sheets

Examples related to datetime-format

Examples related to date-arithmetic