Re: [libreoffice-users] Re: difference in hours between two times

At 17:55 24/05/2018 -0400, James Lockie wrote:
> 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.

At 15:07 24/05/2018 -0400, James wrote:
> 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


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy