Date: prev next · Thread: first prev next last
2018 Archives by date, by thread · List index


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

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.