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

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

If you don't want multiple cells
and
if you want years/months/days missing if 0 and singular/plural

Code: Select all   Expand viewCollapse view
`=IF(YEARS(B1;B2;0)=0;"";YEARS(B1;B2;0)&IF(YEARS(B1;B2;0)>1;" years ";" year "))&IF(MOD(MONTHS(B1;B2;0);12)=0;"";MOD(MONTHS(B1;B2;0);12)&IF(MOD(MONTHS(B1;B2;0);12)>1;" months ";" month "))&IF(DAY(B1)=DAY(B2);"";B2-EDATE(B1;MONTHS(B1;B2;0))&IF(B2-EDATE(B1;MONTHS(B1;B2;0))>1;" days";" day"))`
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:If you don't want multiple cells
and
if you want years/months/days missing if 0 and singular/plural

Code: Select all   Expand viewCollapse view
`=IF(YEARS(B1;B2;0)=0;"";YEARS(B1;B2;0)&IF(YEARS(B1;B2;0)>1;" years ";" year "))&IF(MOD(MONTHS(B1;B2;0);12)=0;"";MOD(MONTHS(B1;B2;0);12)&IF(MOD(MONTHS(B1;B2;0);12)>1;" months ";" month "))&IF(DAY(B1)=DAY(B2);"";B2-EDATE(B1;MONTHS(B1;B2;0))&IF(B2-EDATE(B1;MONTHS(B1;B2;0))>1;" days";" day")) `

IT WORKS!!!
I knew you guys could help, Many 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.

RayBir wrote:
Villeroy wrote:- or can you import sheets from OO to Libre?

Beware of that import! I tried, but many (if not most) of my dates were messed up, so I quickly deleted LibreO from my computer.
Obviously OO and LO use different year systems, but it's beyond my competence and available time to find out how to remedy that and then check if indeed all my hundreds of dates actually became correct or not.
okjhum

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

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

okjhum wrote:
RayBir wrote:
Villeroy wrote:- or can you import sheets from OO to Libre?

Beware of that import! I tried, but many (if not most) of my dates were messed up, so I quickly deleted LibreO from my computer.
Obviously OO and LO use different year systems, but it's beyond my competence and available time to find out how to remedy that and then check if indeed all my hundreds of dates actually became correct or not.

Thanks but i have no need now as the formula given above for me works 100%!
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.

If you use /Tools /Options /OpenOffice Calc : Calculate you have a choice of three date origins. It is important to select the correct one of these when moving between OpenOffice and LibreOffice.
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.

okjhum wrote:
RayBir wrote:
Villeroy wrote:- or can you import sheets from OO to Libre?

Beware of that import! I tried, but many (if not most) of my dates were messed up, so I quickly deleted LibreO from my computer.
Obviously OO and LO use different year systems, but it's beyond my competence and available time to find out how to remedy that and then check if indeed all my hundreds of dates actually became correct or not.

There is nothing to import. Both applications use the same file format. They do not use different year systems. AOO has a bug which adds a strange entry into its main configuration file which shifts all dates by 4 years +2 days (1462 days). When you open these files with LibreOffice's normal settings, dates are 4 years behind.

You don't give us any specific details. I assume that you suffer from this bug in AOO: viewtopic.php?f=9&t=72644 which will never be fixed since AOO is no longer under active development.
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

Previous