Page 1 of 1

[Solved] Formatting date calculation

Posted: Fri Mar 20, 2020 5:50 pm
by Ssronica
I'm looking to show how long it took something on my spreadsheet to sell.

The formula is easy enough: if A2 shows the date something was added to inventory, and B2 shows the date it sold, I can just =B2-A2. Which works fine if the time period is low enough.

Where I'm running out of ideas, is if the item takes months to sell. Instead of Calc returning a value of 185 days, I sort of need it to say "6 months, 3 days" or whatever. I don't even know if that's possible.

My actual formula: =IF(AC150=0;"";(AC150-AB150)&" DAYS")


Any help totally gratefully appreciated!

Re: Formatting date calculation

Posted: Fri Mar 20, 2020 5:56 pm
by Zizi64
Please upload your ODF type sample file here.

Re: Formatting date calculation

Posted: Fri Mar 20, 2020 6:02 pm
by Ssronica
Hi Zizi,

Thanks for responding - done.

Re: Formatting date calculation

Posted: Fri Mar 20, 2020 6:45 pm
by Zizi64
It is work in my LibreOffice 6.2.8

Code: Select all

=DATEDIF(A5;B5;"y")&" Years "&DATEDIF(A5;B5;"ym")&" Months "&DATEDIF(A5;B5;"md")&" Days "
sample spreadsheet.ods
(11.01 KiB) Downloaded 111 times
____________________________
OpenOffice 3.4.1;Win7
Your office version is archaic old.

Re: Formatting date calculation

Posted: Fri Mar 20, 2020 6:58 pm
by Ssronica
Thanks Zizi.

I did try it in Apache OpenOffice and it didn't work. Do you have anything that might work in Apache?

Re: Formatting date calculation

Posted: Fri Mar 20, 2020 7:01 pm
by Zizi64
Try the LibreOffice. The DATEDIF is an additional function of the LibreOffice, for a higher compatibility with the Excel files. Apache OpenOffice has not such function.
viewtopic.php?f=75&t=101390

You can calculate nearly similar result based on the elapsed days with average 30 days long months,

Re: Formatting date calculation

Posted: Fri Mar 20, 2020 7:05 pm
by Ssronica
Thanks again.

I have a fairly extensive spreadsheet - do you know if it will be compatible in LibreOffice?

Re: Formatting date calculation

Posted: Fri Mar 20, 2020 7:07 pm
by Zizi64
Try it. I suppose, the answer is "yes".

Re: Formatting date calculation

Posted: Fri Mar 20, 2020 8:23 pm
by Lupp
There may be reasons (already discussed mostly in this forum) to move to LibreOffice - and I myself mostly use it for many years now.
The DATEDIF() function, however is an MS Excel toy, to which the inventors themselves didn't even spend the second "F". It is complicated (see suggested formula, useless as far as further calculations are concerned, and it is fogging the fact that "month" and "year" aren't units of time but calendaric makeshifts.
If you aren't definitely forced to be compatible with Excel (as far as possible), this specific "feature" isn't the best reason to change.

I would assume you are clever enough to interpret the result of

Code: Select all

=(EndDate - StartDate) / 30.42
formatted to
0.00" months roughly"
as the information you actually want.

Re: Formatting date calculation

Posted: Fri Mar 20, 2020 10:58 pm
by MrProgrammer
Ssronica wrote:Instead of Calc returning a value of 185 days, I sort of need it to say "6 months, 3 days"
This is answered for OpenOffice in [Tutorial] Calc date formulas, Section D. Determine years/months/weeks/days between dates. I believe you want the formula shown for "As nnM nnD" (months and days).
Zizi64 wrote:Apache OpenOffice has not such function.
AOO does not have DATEDIF but can easily perform the calculation.
Lupp wrote:=(EndDate - StartDate) / 30.42
As you would know, this is an estimate which may be particularly vulnerable in cases where the result is close to an integer, for example from February 1 to March 2, where the quotient is 0.986. AOO provides functions for an exact calculation: 1 month and 1 day.
Ssronica wrote:I have a fairly extensive spreadsheet - do you know if it will be compatible in LibreOffice?
Switch office suites if you like, but there is no need for you to do so just to resolve this topic. The formula in the tutorial will work with OpenOfficce 3.4.1.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

Re: Formatting date calculation

Posted: Sat Mar 21, 2020 8:41 am
by Ssronica
Thanks MrProgrammer.

I looked at the information you linked to, especially the "As nnM nnD" bit, but I don't have the skill to implement it. Would you mind providing the formula it uses in the context of the .ods spreadsheet I uploaded?

Re: Formatting date calculation

Posted: Sat Mar 21, 2020 10:22 am
by Villeroy
Dividing the days by 7 gives weeks
=(end-start)/7
=(B5-A5)/7

In the formulas provided by MrProgrammer you only need to replace the word "start" with a reference to the start date and the word "end" with a reference to the end date:
=TEXT(MONTHS(start;end;0);"0\M ")&TEXT(end-EDATE(start;MONTHS(start;end;0));"0\D")
=TEXT(MONTHS(A5;B5;0);"0\M ")&TEXT(B5-EDATE(A5;MONTHS(A5;B5;0));"0\D")

Re: Formatting date calculation

Posted: Sat Mar 21, 2020 2:04 pm
by Lupp
MrProgrammer wrote:...AOO provides functions for an exact calculation: 1 month and 1 day.
My criticism may be seen as fundamental and re-critisized therefore, but it concerned some tangible points:
- Taken as a duration the result is misleading. It cannot tell how long the months were (28<=MonthLength<=31).
- The missing information cannot be retrieved from the result for further calculations.
- The question was seeking a formatted result, but
- - obtained result is generic text. It cannot be referred to as a number. (Resorting to original Start and End needed.)
- If any further evaluation is intended the numbers of years, months, and days need to be extracted.
- The formula given by Zizi64 (for LibO with DATEDIF) will not return what the questioner expects in special cases.
- - This specifically if two related cases are compared. Example:
- - - With Start=2019-02-28 and End=2020-03-01 it returns "1 Years 0 Months 2 Days".
- - - With Start=2019-02-28 and End=2021-03-01 it returns "2 Years 0 Months 1 Days".
- - - Why? Isn't the second span "exactly" on year longer than the first one?
- - - (It's the same with the formula from chapter D of the linked tutorial.)
- - - Yes. I know the reason, and my example doesn't try to hide it.
- Treating unwanted effects or doubtable behaviour of "standard" functions will be complicated and won't pay.
- To word it explicitly: There isn't something like an "exact calculation" in the case.
- - There must be an unmistakable specification for every case to be able to tell if a result is correct/exact.
- - Putting the results of a certain way of calculation into the role of a specification is evil.
MrProgrammer wrote:
Lupp wrote: =(EndDate - StartDate) / 30.42
As you would know, this is an estimate which may be particularly vulnerable...
I don't understand the word "vulnerable" in the context. The reason should be explained sufficiently above.
In the other respect: I know, of course. Moreover this is intended. An estimate not fogging its nature may be better, imo, than a result pretending to be "exact" where the term exact is doutable itself due to the context.
Eventually the user needs to decide if the information he(f/m) got is sufficient or if he needs to look at Start and End in the specific case. An automated further evaluation of the result should actually be considered vulnerable.

In short: We shouldn't try to use calendaric terms like "year", "month", "day" as if they are units of time.
Where traditional ways (in calculation of interest e.g.) is defined with respect to the numbers of years, months, days, we need three numeric results which can easily be referred to by formulas.

Re: Formatting date calculation

Posted: Sat Mar 21, 2020 2:24 pm
by Villeroy
We all deal with this kind of information every day. It is important to understand that information is not the same as data, though. The text formulas turn data (day numbers) into human friendly textual information. If you need more data processing, you can't resort to the informational text data, though. You have to use the numeric time spans for data pilots, charts and further calculations based on durations.

Re: Formatting date calculation

Posted: Sat Mar 21, 2020 5:05 pm
by Ssronica
Thanks a lot Villeroy, that's such a great help.

One more question - on the .ods I provided, the formula you provided returns "7M 5D". Is there a way to format that to read more naturally, eg "7 months, 5 days" ?

Re: Formatting date calculation

Posted: Sat Mar 21, 2020 6:45 pm
by Villeroy
Replace "0\D" with "0 ""days"""
Replace "0\M" with "0 ""months"""

Re: [Solved] Formatting date calculation

Posted: Sat Mar 21, 2020 6:53 pm
by Ssronica
Danke, Villeroy. That was a huge help.

And thank you Zizi, Lupp and MrProgrammer for your contributions. I'm always amazed at you guys' knowledge and willingness to help!

Marked this as solved.