[Solved] Formatting date calculation

Discuss the spreadsheet application
Post Reply
Ssronica
Posts: 33
Joined: Wed Sep 25, 2013 11:41 pm

[Solved] Formatting date calculation

Post 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!
Attachments
sample spreadsheet.ods
(9.18 KiB) Downloaded 124 times
Last edited by Ssronica on Sat Mar 21, 2020 6:51 pm, edited 2 times in total.
OpenOffice 3.4.1;Win7
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Formatting date calculation

Post by Zizi64 »

Please upload your ODF type sample file here.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Ssronica
Posts: 33
Joined: Wed Sep 25, 2013 11:41 pm

Re: Formatting date calculation

Post by Ssronica »

Hi Zizi,

Thanks for responding - done.
OpenOffice 3.4.1;Win7
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Formatting date calculation

Post 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.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Ssronica
Posts: 33
Joined: Wed Sep 25, 2013 11:41 pm

Re: Formatting date calculation

Post 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?
OpenOffice 3.4.1;Win7
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Formatting date calculation

Post 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,
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Ssronica
Posts: 33
Joined: Wed Sep 25, 2013 11:41 pm

Re: Formatting date calculation

Post by Ssronica »

Thanks again.

I have a fairly extensive spreadsheet - do you know if it will be compatible in LibreOffice?
OpenOffice 3.4.1;Win7
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Formatting date calculation

Post by Zizi64 »

Try it. I suppose, the answer is "yes".
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Formatting date calculation

Post 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.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Formatting date calculation

Post 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.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Ssronica
Posts: 33
Joined: Wed Sep 25, 2013 11:41 pm

Re: Formatting date calculation

Post 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?
OpenOffice 3.4.1;Win7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Formatting date calculation

Post 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")
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Formatting date calculation

Post 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.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Formatting date calculation

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ssronica
Posts: 33
Joined: Wed Sep 25, 2013 11:41 pm

Re: Formatting date calculation

Post 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" ?
OpenOffice 3.4.1;Win7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Formatting date calculation

Post by Villeroy »

Replace "0\D" with "0 ""days"""
Replace "0\M" with "0 ""months"""
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ssronica
Posts: 33
Joined: Wed Sep 25, 2013 11:41 pm

Re: [Solved] Formatting date calculation

Post 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.
OpenOffice 3.4.1;Win7
Post Reply