How to calculate in a period: Years, Months and days.

Discuss the spreadsheet application
Juanchio88
Posts: 4
Joined: Thu Jan 26, 2017 6:48 pm

How to calculate in a period: Years, Months and days.

Post by Juanchio88 »

Hi everyone, i already did a search on the topic yet im reallty new to using Calc (or excel, to be honest). My questiong might be easy to explay yet im a real newbie haha, well here it goes:

I want to know the Years that have passed in a period, then how many months have left since the last year ended and how many days that last month have.

For example of what im looking for:

Start date: 12/May/2003
End Date: 30/Jun/2006

Years: 3
Months: 1 Month (Since I scept to get how many FULL months have hapenned after the third years occurs, therefore 12/May/2006 is where the yeard ended.)
Days: 18 Days (Same concept as months).

With the years i have no problem, but after that things gets way to messy for me. I tried in a friends PC with office and found a formula to do so, however i want to learn and use Calc from now on, so thats why im here. Thanks so much to all of you guys!
OpenOffice 3.1 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to calculate in a period: Years, Months and days.

Post by Villeroy »

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
Juanchio88
Posts: 4
Joined: Thu Jan 26, 2017 6:48 pm

Re: How to calculate in a period: Years, Months and days.

Post by Juanchio88 »

Yeah, I knew that about excel, what I wonder is there anything like it in Calc?
OpenOffice 3.1 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to calculate in a period: Years, Months and days.

Post by Villeroy »

Base has a datediff function too.
Calc has add-in functions not compatible with other applications:
YEARS
MONTHS
WEEKS
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
Juanchio88
Posts: 4
Joined: Thu Jan 26, 2017 6:48 pm

Re: How to calculate in a period: Years, Months and days.

Post by Juanchio88 »

Villeroy wrote:Base has a datediff function too.
Calc has add-in functions not compatible with other applications:
YEARS
MONTHS
WEEKS
Yeah, so far I have figure out to do the Years part of it, using =Years(b1;b2;0)
I´m curious if i can do something like

=Months (Deduct the years from the previous cell, so everything left would be the months)
=Days (Do the same that did for months)

Also sorry if the solution is too obvious or maybe im asking things outside the original question, but thanks so much for taking time to reply.
OpenOffice 3.1 on Windows 7
gerard24
Volunteer
Posts: 958
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

Re: How to calculate in a period: Years, Months and days.

Post by gerard24 »

Datedif_YMD.jpg
LibreOffice 6.4.5 on Windows 10
Juanchio88
Posts: 4
Joined: Thu Jan 26, 2017 6:48 pm

Re: How to calculate in a period: Years, Months and days.

Post by Juanchio88 »

gerard24 wrote:
Datedif_YMD.jpg
This worked almost like a charm, however im having a little issue with Days, It returns: "17/01/00". I guess 17 are the days left of that month, right? And Months returns "2", no idea why,

Im using that same formula you provided for each cell.

http://imgur.com/a/IG96n
OpenOffice 3.1 on Windows 7
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: How to calculate in a period: Years, Months and days.

Post by FJCC »

You are getting "17/01/00" in B6 because the number is being interpreted as a date and 17-Jan-1900 has the value 18. Use the menu Format -> Cells to set the cell format to be an integer. I don't know why you are getting 2 for the months. gerard24's formula works for me. Can you upload the document? After you click Post Reply there is an Upload Attachment tab just below the window where you type a response.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
soby
Volunteer
Posts: 150
Joined: Sat Oct 29, 2016 10:49 am

Re: How to calculate in a period: Years, Months and days.

Post by soby »

another example
Attachments
Years and motnh calculation.ods
(8.99 KiB) Downloaded 498 times
Libre Office 6.1 dev homebuild Open Office 4.1.5 on Slackware64 current
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to calculate in a period: Years, Months and days.

Post by Villeroy »

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
okjhum
Posts: 24
Joined: Sat Apr 26, 2008 6:42 pm
Location: Sweden

Re: How to calculate in a period: Years, Months and days.

Post by okjhum »

gerard24's formulae (Jan 26, 2017) for months and days don't work for me.

soby's formulae (Jan 26, 2017) only work as long as the numerical values of the enddate month and/or day are/is larger than of the startdate.

In that example the period from 2003-05-12 to 2006-06-30 is 3 years 1 months 18 days.

But from 2003-06-30 to 2006-05-12 yields 3 years -1 months -18 days with negative numbers, which obviously is wrong. Maybe some complicated IF algorithms would be needed to fix the carry-over between days, months and years. That's above my competence.
The correct answer should be 2 years 11 months and 12 days or so. But the number of days would also vary depending on which months are involved. Too difficult for me to solve. :(
Hope somebody still reads this old thread.
Last edited by okjhum on Thu Jun 13, 2019 2:08 pm, edited 1 time in total.
Windows 10: Apache OpenOffice v 4.1.9 (Build:9805) - Rev 561082130a (Date 2021-01-08 16:08)
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: How to calculate in a period: Years, Months and days.

Post by robleyd »

03-05-12
That could be anything. Americans would see it as March 5 in a century ending in 12. Most of the rest of the world would see it as May 3 in a century ending in 12. You seem to be treating it as May 12 in a century ending in 03.

Look at the link Villeroy posted, especially sections 2 and 3

Someone else will probably give you guidance on using a standard, non-confusing date format
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
okjhum
Posts: 24
Joined: Sat Apr 26, 2008 6:42 pm
Location: Sweden

Re: How to calculate in a period: Years, Months and days.

Post by okjhum »

I came up with this unelegant formula that however seems to give fairly but not completely correct answers:
=TRUNC((end-start)/365,25) &" yr " &INT(((end-start)/365,25-TRUNC((end-start)/365,25))*12) &" mo " &TRUNC((((end-start)/365,25-TRUNC((end-start)/365,25))*12-TRUNC(((end-start)/365,25-TRUNC((end-start)/365,25))*12))*30) &" d"

start end → result:
2003-05-12 2006-06-30 → 3 yr 1 mo 18 d
2003-06-30 2006-05-12 → 2 yr 10 mo 11 d (is this correct?)

Further trials:
2003-06-30 2006-04-30 → 2 yr 10 mo 0 d
2006-04-30 2006-05-12 → 0 yr 0 mo 11 d (why 11 days??)

2003-06-01 2006-05-01 → 2 yr 10 mo 29 d (why not 2 yr 11 mo 0 d?)
2003-06-01 2006-05-30 → 2 yr 11 mo 28 d
2003-06-01 2006-05-31 → 2 yr 11 mo 29 d
2003-06-01 2006-06-01 → 3 yr 0 mo 0 d
Windows 10: Apache OpenOffice v 4.1.9 (Build:9805) - Rev 561082130a (Date 2021-01-08 16:08)
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: How to calculate in a period: Years, Months and days.

Post by Lupp »

I would be interested in a real-world example where results of such formulas are actually useful or even needed. Obviously date differences expressed the years-months-days way can wrongly look the_same / different in cases where they are actally different / the_same when correctly expressed in real units.

Despite my doubts I anwered a similar question in the ask.libreoffice.org /en and made a demo for it.
I now adapted this demo to suit better the current thread. In parallel it has a solution based on standard functions and one based on the DATEDIF() function still not implemented in AOO (but in LibO V 3.6.0 or higher). See attachment.

I didn't try to squeze the solution by standard functions into one formula, but used helpers instead. Otherwise I would have ended up with just another monster. Calendaric calculations are actually rather demanding. Not without reason astronomers stick to the "Julian year" in a sense.
okjhum wrote:...formula ... seems to give fairly but not completely correct answers
Sorry. That's not acceptable. Fairly correct must be correct. If there are exceptions they should be named and the reasons should be analysed. Then the given suggestion must be set under the respective restrictions. I applyed the suggested "fairly correct" formula many times on a sample of 250 random pairs of dates (within a reasonable range) and got a "hit quote" of slightly above 30%.
Attachments
aoo87153playWithDateDifferences_1.ods
(114.2 KiB) Downloaded 179 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
okjhum
Posts: 24
Joined: Sat Apr 26, 2008 6:42 pm
Location: Sweden

Re: How to calculate in a period: Years, Months and days.

Post by okjhum »

Ah, "why 11 days?" → because the first day is a day 1, not zero. :knock: So for inclusive calculation I added +1 at the end. I also changed from TRUNC to ROUND in the beginning of the day calculation. When I put ROUND at more places, I got bigger errors.
Finally, I now multiply the last factor not by 30 but by 30,4166666667, which is the arithmetic mean number of days in a month.
Thus the refined formula:

=TRUNC((end-start)/365,25) &" yr " &INT(((end-start)/365,25-TRUNC((end-start)/365,25))*12) &" mo " &ROUND((((end-start)/365,25-TRUNC((end-start)/365,25))*12-TRUNC(((end-start)/365,25-TRUNC((end-start)/365,25))*12))*30,4166666667
) &" d"

So, with these modifications, the same table as above now becomes:

start to end → result
2003-05-12 to 2006-06-30 → 3 yr 1 mo 19 d
2003-06-30 to 2006-05-12 → 2 yr 10 mo 12 d

2003-06-30 to 2006-04-30 → 2 yr 10 mo 0 d
2006-04-30 to 2006-05-12 → 0 yr 0 mo 12 d

2003-06-01 to 2006-05-30 → 2 yr 11 mo 29 d
2003-06-01 to 2006-05-31 → 2 yr 11 mo 30 d
2003-06-01 to 2006-06-01 → 3 yr 0 mo 0 d


My dyscalculia prevents me from judging whether this is sufficiently correct, or still only "fairly." :-)

I didn't know abnout astronomers and the Julian calendar. Can we use it too? I am astronomically impressed that they can calculate not only the exact, complicated trajectory for a many-years-long journey round planets with ever increasing speed and changing directions, but even determine the exact second when the vessel should land on a comet. Incredible. 8-)
Windows 10: Apache OpenOffice v 4.1.9 (Build:9805) - Rev 561082130a (Date 2021-01-08 16:08)
okjhum
Posts: 24
Joined: Sat Apr 26, 2008 6:42 pm
Location: Sweden

Re: How to calculate in a period: Years, Months and days.

Post by okjhum »

Lupp wrote:...]Sorry. That's not acceptable. Fairly correct must be correct. If there are exceptions they should be named and the reasons should be analysed. Then the given suggestion must be set under the respective restrictions. I applyed the suggested "fairly correct" formula many times on a sample of 250 random pairs of dates (within a reasonable range) and got a "hit quote" of slightly above 30%.
Thank you Lupp for your interesting spreadsheets and impressive effort into this!
I don't understad why I can't get your resD columns displayed as integers. Now they look like strange dates on my computer (Win 10).

My new formula (without the +1 term) now scored 36-44 percent correct results. Still not acceptable. So your method, of course, is the better.
Windows 10: Apache OpenOffice v 4.1.9 (Build:9805) - Rev 561082130a (Date 2021-01-08 16:08)
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: How to calculate in a period: Years, Months and days.

Post by Lupp »

okjhum wrote:I don't understad why I can't get your resD columns displayed as integers. Now they look like strange dates on my computer (Win 10).
That's nothing to do with your computer or the OS, but with AOO (of which I still don't know the version you are using). It's one of the strange effects of automatic formatting if 'Nmbers' format 'General' is set. The first operand in the formula of that column is a reference to a cell formatted as date. AOO tries to be "smart", looks for that format, and causes the output of the formula to show in the same format. (LibO meanwile reduced the cases where this kind of "smartness" really is a PITA.) You can select the respective column(s) and format them explicitly to sbe suitable for natural numbers. The format code you need to enter in place of 'General' is a simple 0 (zero).

Concerning "better" methods: As I already mentioned calendaric calculations tend to get tricky. That's a fact you can best cope with wiriting a piece of program in a GeneralPurposeProgrammingLanguage. The primitive kind of "functional programming" supported by spreadsheets is next to completely inapt concerning the task. At best it ends up with (probably?) correct, but monstrous formulas.

Since user programming shouldn't be encouraged too much for many reasons, it was an acceptable idea to create a built-in function for the task under discussion here, hiding the problems with the calendar in its internal code. That function is DATEDIF(). It is specified for the "Large Group Evaluator" by ODF papers, but not yet implemented for AOO.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
okjhum
Posts: 24
Joined: Sat Apr 26, 2008 6:42 pm
Location: Sweden

Re: How to calculate in a period: Years, Months and days.

Post by okjhum »

Lupp wrote:I would be interested in a real-world example where results of such formulas are actually useful or even needed.
I'm keeping a log of my migrating RA attacks and symptoms at various locations (joints and tendons) to detect any trends in progress or regress. The fine details of time (+/- one or more days) are completely unimportant, but the problem of time calculation intrigued me and lured me into wasting far too much time on this! :-D
Having great difficulties with simple arithmetics, the complex subject fascinates me. Solving problems is great fun. :ucrazy: :D :lol:
Windows 10: Apache OpenOffice v 4.1.9 (Build:9805) - Rev 561082130a (Date 2021-01-08 16:08)
okjhum
Posts: 24
Joined: Sat Apr 26, 2008 6:42 pm
Location: Sweden

Re: How to calculate in a period: Years, Months and days.

Post by okjhum »

Lieber Herr Lupp aus München (wo meine Schwester lebt - aber ich in Schweden)! Vielen Dank!
I tried to concatenate your helper formulae from cells D:J into one monster formula but failed. As I don't want to have too many columns in my pages, I'll stick to my approximate formula for the time being. The error seems never to exceed 1 day, anyway. Maybe later I'll make and put helper cells outside the displayed page, if I ever feel for it. I will then take the liberty to use your fomulae that obviously do work (though they are incomprehensible to me, and I didn't manage to add +1 day for inclusive calculation). :-)
Windows 10: Apache OpenOffice v 4.1.9 (Build:9805) - Rev 561082130a (Date 2021-01-08 16:08)
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: How to calculate in a period: Years, Months and days.

Post by RusselB »

I've been working on a version of DATEDIF for OpenOffice for a while, when I've had the time, in reference to another (similar) topic.
So far I have a lot of the problems worked out, but not all of them.
The easiest method is to switch to LibreOffice for this project, and use the included DATEDIF function.

If you prefer OpenOffice, you can have both installed at the same time.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: How to calculate in a period: Years, Months and days.

Post by RoryOF »

Quite frankly, Months are an irregular unit. Is a month 28, 29, 30 or 31 days? If you are trying to map a recurring problem, days are a better unit.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
okjhum
Posts: 24
Joined: Sat Apr 26, 2008 6:42 pm
Location: Sweden

Re: How to calculate in a period: Years, Months and days.

Post by okjhum »

RusselB wrote: The easiest method is to switch to LibreOffice for this project, and use the included DATEDIF function.
If you prefer OpenOffice, you can have both installed at the same time.
LibreOffice seemed so nice, so I installed it ... but in the conversion from OpenOffice, most of my dates were so severely disordered that I quickly uninstalled it and reverted to the back-up copies of my speadsheets. :-(
Windows 10: Apache OpenOffice v 4.1.9 (Build:9805) - Rev 561082130a (Date 2021-01-08 16:08)
okjhum
Posts: 24
Joined: Sat Apr 26, 2008 6:42 pm
Location: Sweden

Re: How to calculate in a period: Years, Months and days.

Post by okjhum »

RoryOF wrote:Quite frankly, Months are an irregular unit. Is a month 28, 29, 30 or 31 days? If you are trying to map a recurring problem, days are a better unit.
True, very true, but, e.g., 967 days doesn't tell my dysarithmetic brain much, and the exactness is not that important. So I prefer something comprehensible, like 2 years, 7 months and some 23 days - or whatever it might be. :-)
Even "almost 2 years and 8 months" is better for my purpose.
Windows 10: Apache OpenOffice v 4.1.9 (Build:9805) - Rev 561082130a (Date 2021-01-08 16:08)
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: How to calculate in a period: Years, Months and days.

Post by RusselB »

okjhum wrote:
RusselB wrote: The easiest method is to switch to LibreOffice for this project, and use the included DATEDIF function.
If you prefer OpenOffice, you can have both installed at the same time.
LibreOffice seemed so nice, so I installed it ... but in the conversion from OpenOffice, most of my dates were so severely disordered that I quickly uninstalled it and reverted to the back-up copies of my speadsheets. :-(
What format were your spreadsheets saved in? If not Open Document Format (.ods extension for spreadsheets), please reconsider your usage of non-standard formats. The Open Document Format is an internationally recognized file format.
Was it just one spreadsheet or all? Can you provide us with an example of a spreadsheet that opens properly in OpenOffice, but not in LibreOffice?
Please read [Forum] How to attach a document for information on anonymizing your data.
Since the dates are important to the problem you reported, I'm going to ask that you not change any of the dates. Without any other reference data, the dates become anonymous even in date format.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to calculate in a period: Years, Months and days.

Post by Villeroy »

The easiest solution to all these questions is the pivot table (aka "data pilot") https://wiki.openoffice.org/wiki/Docume ... /DataPilot
Calc Guide wrote:Many requests for software support are caused by complicated formulas and solutions to simple day to day procedures. For more efficient and effective solutions, use the DataPilot, a tool for combining, comparing, and analyzing large amounts of data easily. By using the DataPilot, you can view different summaries of the source data, display the details of areas of interest, and create reports, whether you are a beginner or an intermediate or advanced user.
All it takes is a normalized table with or without calculated fields, any order of rows, any order of columns.
Some recent topics:
viewtopic.php?t=97790&p=469102#p469102
viewtopic.php?t=97746&p=468896#p468896
viewtopic.php?t=97603&p=468221#p468221
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
RayBir
Posts: 20
Joined: Mon May 20, 2019 12:10 pm
Location: Sweden

Re: How to calculate in a period: Years, Months and days.

Post by RayBir »

gerard24 wrote:
Datedif_YMD.jpg
I read this and it works great - if you seperate Year-Month-Days in seperate rows.
But is it possible to have those three functions in the same cell please?
So that it reads "43yrs-4months-20days" for exampel in the same cell?

Thanks.
OpenOffice 4.1.3 on MacOS Catalina 10.15.1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to calculate in a period: Years, Months and days.

Post by Villeroy »

=CONCATENATE(B4 ; "yrs-" ; B5 ; "months-" ; B6 ; "days")
or
=B4 & "yrs-" & B5 & "months-" & B6&"days"

and by the way: LibreOffice has a DATEDIF function.
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
RayBir
Posts: 20
Joined: Mon May 20, 2019 12:10 pm
Location: Sweden

Re: How to calculate in a period: Years, Months and days.

Post by RayBir »

Villeroy wrote:=CONCATENATE(B4 ; "yrs-" ; B5 ; "months-" ; B6 ; "days")
or
=B4 & "yrs-" & B5 & "months-" & B6&"days"
Thanks for a very quick response!

So in other words, the original formula still has to exist, but maybe in hidden cells somewhere, then use your new formula to make a clearer more readable text collecting data from the "other/hidden" cells?

Yes i read in other articles about that. But i have too mant OpenOffice documents to change - or can you import sheets from OO to Libre?
Last edited by RayBir on Thu Jul 18, 2019 4:17 pm, edited 1 time in total.
OpenOffice 4.1.3 on MacOS Catalina 10.15.1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to calculate in a period: Years, Months and days.

Post by Villeroy »

Replace the reference to B4 with the formula of B4 without the leading =
Same with the other references.
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
RayBir
Posts: 20
Joined: Mon May 20, 2019 12:10 pm
Location: Sweden

Re: How to calculate in a period: Years, Months and days.

Post by RayBir »

Villeroy wrote:Replace the reference to B4 with the formula of B4 without the leading =
Same with the other references.
Not sure i understand:

If i have a birthdate for ex and i have todays date (DATE), how would that formula look in one cell please?
OpenOffice 4.1.3 on MacOS Catalina 10.15.1
Post Reply