[Solved] Formatting date calculation
[Solved] Formatting date calculation
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!
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
Re: Formatting date calculation
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.
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.
Re: Formatting date calculation
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 "
Your office version is archaic old.____________________________
OpenOffice 3.4.1;Win7
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.
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.
Re: Formatting date calculation
Thanks Zizi.
I did try it in Apache OpenOffice and it didn't work. Do you have anything that might work in Apache?
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
Re: Formatting date calculation
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,
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.
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.
Re: Formatting date calculation
Thanks again.
I have a fairly extensive spreadsheet - do you know if it will be compatible in LibreOffice?
I have a fairly extensive spreadsheet - do you know if it will be compatible in LibreOffice?
OpenOffice 3.4.1;Win7
Re: Formatting date calculation
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.
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.
Re: Formatting date calculation
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 ofas the information you actually want.
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"
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
- MrProgrammer
- Moderator
- Posts: 4895
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Formatting date calculation
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).Ssronica wrote:Instead of Calc returning a value of 185 days, I sort of need it to say "6 months, 3 days"
AOO does not have DATEDIF but can easily perform the calculation.Zizi64 wrote:Apache OpenOffice has not such function.
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.Lupp wrote:=(EndDate - StartDate) / 30.42
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.Ssronica wrote:I have a fairly extensive spreadsheet - do you know if it will be compatible in LibreOffice?
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).
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).
Re: Formatting date calculation
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?
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
Re: Formatting date calculation
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")
=(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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Formatting date calculation
My criticism may be seen as fundamental and re-critisized therefore, but it concerned some tangible points:MrProgrammer wrote:...AOO provides functions for an exact calculation: 1 month and 1 day.
- 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.
I don't understand the word "vulnerable" in the context. The reason should be explained sufficiently above.MrProgrammer wrote:As you would know, this is an estimate which may be particularly vulnerable...Lupp wrote: =(EndDate - StartDate) / 30.42
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
---
Lupp from München
Re: Formatting date calculation
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Formatting date calculation
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" ?
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
Re: Formatting date calculation
Replace "0\D" with "0 ""days"""
Replace "0\M" with "0 ""months"""
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Formatting date calculation
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.
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