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

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

The answer is


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:

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

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

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

Examples related to time

Date to milliseconds and back to date in Swift How to manage Angular2 "expression has changed after it was checked" exception when a component property depends on current datetime how to sort pandas dataframe from one column Convert time.Time to string How to get current time in python and break up into year, month, day, hour, minute? Xcode swift am/pm time to 24 hour format How to add/subtract time (hours, minutes, etc.) from a Pandas DataFrame.Index whos objects are of type datetime.time? What does this format means T00:00:00.000Z? How can I parse / create a date time stamp formatted with fractional seconds UTC timezone (ISO 8601, RFC 3339) in Swift? Extract time from moment js object

Examples related to google-sheets

Import data into Google Colaboratory Is there a Google Sheets formula to put the name of the sheet into a cell? How to label scatterplot points by name? Adding Buttons To Google Sheets and Set value to Cells on clicking How to automatically import data from uploaded CSV or XLS file into Google Sheets How to negate 'isblank' function How can I copy a conditional formatting from one document to another? Multiple IF statements between number ranges Apply formula to the entire column How to define global variable in Google Apps Script

Examples related to datetime-format

How do I convert 2018-04-10T04:00:00.000Z string to DateTime? Unable to obtain LocalDateTime from TemporalAccessor when parsing LocalDateTime (Java 8) How can I create basic timestamps or dates? (Python 3.4) Format Instant to String Laravel $q->where() between dates How to Convert datetime value to yyyymmddhhmmss in SQL server? AngularJS - convert dates in controller Display DateTime value in dd/mm/yyyy format in Asp.NET MVC Display date in dd/mm/yyyy format in vb.net Convert DataFrame column type from string to datetime, dd/mm/yyyy format

Examples related to date-arithmetic

How to subtract hours from a date in Oracle so it affects the day also MySQL Select last 7 days Calculating days between two dates with Java How to find the duration of difference between two dates in java? Check if year is leap year in javascript Add a month to a Date Get the difference between two dates both In Months and days in sql Oracle Date - How to add years to date How can I do time/hours arithmetic in Google Spreadsheet? Oracle DateTime in Where Clause?