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

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

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
Juanchio88

Posts: 4
Joined: Thu Jan 26, 2017 6:48 pm

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

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

Villeroy
Volunteer

Posts: 27728
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Yeah, I knew that about excel, what I wonder is there anything like it in Calc?
OpenOffice 3.1 on Windows 7
Juanchio88

Posts: 4
Joined: Thu Jan 26, 2017 6:48 pm

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

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, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 27728
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

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
Juanchio88

Posts: 4
Joined: Thu Jan 26, 2017 6:48 pm

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

LibreOffice 6.3.4 on Windows 10
gerard24
Volunteer

Posts: 953
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

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

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
Juanchio88

Posts: 4
Joined: Thu Jan 26, 2017 6:48 pm

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

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.
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7485
Joined: Sat Nov 08, 2008 8:08 pm

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

another example
Attachments
Years and motnh calculation.ods
Libre Office 6.1 dev homebuild Open Office 4.1.5 on Slackware64 current

soby
Volunteer

Posts: 150
Joined: Sat Oct 29, 2016 10:49 am

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

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

Villeroy
Volunteer

Posts: 27728
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

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.
Last edited by okjhum on Thu Jun 13, 2019 2:08 pm, edited 1 time in total.
okjhum

Posts: 13
Joined: Sat Apr 26, 2008 6:42 pm

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

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
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine

robleyd
Moderator

Posts: 3141
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

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

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
okjhum

Posts: 13
Joined: Sat Apr 26, 2008 6:42 pm

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

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
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München

Lupp
Volunteer

Posts: 2665
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

Ah, "why 11 days?" → because the first day is a day 1, not zero. 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.
okjhum

Posts: 13
Joined: Sat Apr 26, 2008 6:42 pm

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

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.
okjhum

Posts: 13
Joined: Sat Apr 26, 2008 6:42 pm

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

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 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München

Lupp
Volunteer

Posts: 2665
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

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!
Having great difficulties with simple arithmetics, the complex subject fascinates me. Solving problems is great fun.
okjhum

Posts: 13
Joined: Sat Apr 26, 2008 6:42 pm

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

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).
okjhum

Posts: 13
Joined: Sat Apr 26, 2008 6:42 pm

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

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 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
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.

RusselB
Moderator

Posts: 5853
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

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

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.7 on Xubuntu 18.04.4 (mostly 64 bit version) and very infrequently on Win2K/XP

RoryOF
Moderator

Posts: 30407
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

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

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.
okjhum

Posts: 13
Joined: Sat Apr 26, 2008 6:42 pm

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

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.
okjhum

Posts: 13
Joined: Sat Apr 26, 2008 6:42 pm

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

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?
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 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
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.

RusselB
Moderator

Posts: 5853
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

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

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, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 27728
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

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

RayBir

Posts: 20
Joined: Mon May 20, 2019 12:10 pm
Location: Sweden

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

=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, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 27728
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

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

RayBir

Posts: 20
Joined: Mon May 20, 2019 12:10 pm
Location: Sweden

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

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, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 27728
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

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

RayBir

Posts: 20
Joined: Mon May 20, 2019 12:10 pm
Location: Sweden

Next