[Solved] Formatting date calculation

Discuss the spreadsheet application

[Solved] Formatting date calculation

Postby Ssronica » Fri Mar 20, 2020 5:50 pm

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 16 times
Last edited by Ssronica on Sat Mar 21, 2020 6:51 pm, edited 2 times in total.
OpenOffice 3.4.1;Win7
Ssronica
 
Posts: 32
Joined: Wed Sep 25, 2013 11:41 pm

Re: Formatting date calculation

Postby Zizi64 » Fri Mar 20, 2020 5:56 pm

Please upload your ODF type sample file here.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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
Zizi64
Volunteer
 
Posts: 9439
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Formatting date calculation

Postby Ssronica » Fri Mar 20, 2020 6:02 pm

Hi Zizi,

Thanks for responding - done.
OpenOffice 3.4.1;Win7
Ssronica
 
Posts: 32
Joined: Wed Sep 25, 2013 11:41 pm

Re: Formatting date calculation

Postby Zizi64 » Fri Mar 20, 2020 6:45 pm

It is work in my LibreOffice 6.2.8

Code: Select all   Expand viewCollapse view
=DATEDIF(A5;B5;"y")&" Years "&DATEDIF(A5;B5;"ym")&" Months "&DATEDIF(A5;B5;"md")&" Days "

sample spreadsheet.ods
(11.01 KiB) Downloaded 11 times



____________________________
OpenOffice 3.4.1;Win7

Your office version is archaic old.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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
Zizi64
Volunteer
 
Posts: 9439
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Formatting date calculation

Postby Ssronica » Fri Mar 20, 2020 6:58 pm

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
Ssronica
 
Posts: 32
Joined: Wed Sep 25, 2013 11:41 pm

Re: Formatting date calculation

Postby Zizi64 » Fri Mar 20, 2020 7:01 pm

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; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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
Zizi64
Volunteer
 
Posts: 9439
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Formatting date calculation

Postby Ssronica » Fri Mar 20, 2020 7:05 pm

Thanks again.

I have a fairly extensive spreadsheet - do you know if it will be compatible in LibreOffice?
OpenOffice 3.4.1;Win7
Ssronica
 
Posts: 32
Joined: Wed Sep 25, 2013 11:41 pm

Re: Formatting date calculation

Postby Zizi64 » Fri Mar 20, 2020 7:07 pm

Try it. I suppose, the answer is "yes".
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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
Zizi64
Volunteer
 
Posts: 9439
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Formatting date calculation

Postby Lupp » Fri Mar 20, 2020 8:23 pm

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   Expand viewCollapse view
=(EndDate - StartDate) / 30.42
formatted to
0.00" months roughly"
as the information you actually want.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2897
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Formatting date calculation

Postby MrProgrammer » Fri Mar 20, 2020 10:58 pm

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 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3985
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Formatting date calculation

Postby Ssronica » Sat Mar 21, 2020 8:41 am

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
Ssronica
 
Posts: 32
Joined: Wed Sep 25, 2013 11:41 pm

Re: Formatting date calculation

Postby Villeroy » Sat Mar 21, 2020 10:22 am

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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28441
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Formatting date calculation

Postby Lupp » Sat Mar 21, 2020 2:04 pm

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 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2897
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Formatting date calculation

Postby Villeroy » Sat Mar 21, 2020 2:24 pm

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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28441
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Formatting date calculation

Postby Ssronica » Sat Mar 21, 2020 5:05 pm

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
Ssronica
 
Posts: 32
Joined: Wed Sep 25, 2013 11:41 pm

Re: Formatting date calculation

Postby Villeroy » Sat Mar 21, 2020 6:45 pm

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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28441
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Formatting date calculation

Postby Ssronica » Sat Mar 21, 2020 6:53 pm

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
Ssronica
 
Posts: 32
Joined: Wed Sep 25, 2013 11:41 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 10 guests