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

[gerard24]

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

Code: Select all

=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"))

[RayBir]

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

Code: Select all

=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!!!

[okjhum]

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

[RayBir]

- 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%!

[RoryOF]

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.

[Villeroy]

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