difference in hours between two times

users
#1

I have a calc spreadsheet with column A being a date and column B is a time.
I am trying to make a foruma that calculates the difference in days and
another for the difference in hours between 2 rows.

For example:
column A
Wed, May 16, 2018
Mon, May 21, 2018

column B
08:30 PM
12:05 PM

column C
=((A13+B13)-(A12+B12))
=((A14+B14)-(A13+B13))[4.65]

column D
=TRUNC(C13)
=TRUNC(C14) [4]

column E

#2

Is there a reason you can't put A and B in one column.
Format cell as date, define the format DDD MMM DD YYYY HH:MM AM/PM
You can then subtract easily and result is in days or x 24 for hours.

Steve

#3

Is there a reason you can't put A and B in one column.
Format cell as date, define the format DDD MMM DD YYYY HH:MM AM/PM
You can then subtract easily and result is in days or x 24 for hours.

It is hard to enter both a date and time.
It is easy to enter "5/24" and it is converted to Mar 24, 2018.

#4

Divide the difference by an hour, and you get the hours, and then divide that
by 24 or could do the same and divide by 24 hours.

05/16/18 08:30:00 PM 111.58 4.65
05/21/18 12:05:00 PM '=((A2+B2)-(A1+B1))/TIME(1,0,0) '=C1/24

#5

Interesting how people think differently.
Assuming dates have cell format date and time has cell format time;
I came up with days first  = (A2+B2)-(A1+B1) (cell format general)
hours = days*24 (cell format general)
steve

#6

It is hard to enter both a date and time. It is easy to enter "5/24" and it is converted to Mar 24, 2018.

Let's deal with that first. It's true you can enter "5/24" (in an appropriate locale), but the same applies to times. If the time you want is, say, 08:30 PM, what do you enter for that? Presumably "8:30 pm"? You can simply combine those: "5/24 8:30 pm" - with a space between. You can combine formats for dates and times to get exactly what you need.

I have a calc spreadsheet with column A being a date and column B is a time. I am trying to make a formula that calculates the difference in days and another for the difference in hours between 2 rows.

For example:
column A
Wed, May 16, 2018
Mon, May 21, 2018

column B
08:30 PM
12:05 PM

column C
=((A13+B13)-(A12+B12))
=((A14+B14)-(A13+B13))[4.65]

column D
=TRUNC(C13)
=TRUNC(C14) [4]

column E
-
formula? [.65]

You ask for the second value to be "the difference in hours", and you suggest that the answer in your example should be 0.65. But that's the fractional part of the difference in days, not any difference in hours. The difference in hours is 111.58 and the remaining difference in hours, after the whole days have been removed, is 15.58. I'm guessing you want the last figure - or perhaps rounded to 16 or truncated to 15.

TRUNC() is a more flexible function that allows truncation to a specified number of fractional places; unless you are expecting negative differences, why not use INT()?

Unless you need to see the values in column C, you can combine the calculation of days into one in column D:
=INT(A14+B14-(A13+B13))
or, with combined date-time values:
=INT(A14-A13)

For the residual hours, remember that date and time values are stored in the unit of days, so you simply need to multiply by 24 to get hours:
=(A14+B14-(A13+B13)-D14)*24
or:
=(A14-A13-D14)*24

I trust this helps.

Brian Barker

#7

Hi,
If it is pure display you are looking for, then the difference in days
is:
=INT((A2+B2)-(A1+B1)) with a numbers display format (such as # ##0)
the difference in hours is:
=(A2+B2)-(A1+B1) with a display format set to [h]
You can be a bit fancy and set the # days format to be # ##0" days" and
the # hours to be [h]" hours"
I hope this helps.
Rémy.

#8

Since the list doesn't like posting of data directly from spreadsheet format.
Did this testing, and saved as a CSV file.

Start Date,Start Time
05/16/18,08:30:00 PM