Page 2 of 2
Re: How to calculate in a period: Years, Months and days.
Posted: Thu Jul 18, 2019 5:53 pm
by 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"))
Re: How to calculate in a period: Years, Months and days.
Posted: Thu Jul 18, 2019 7:12 pm
by RayBir
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
=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!!!
Re: How to calculate in a period: Years, Months and days.
Posted: Fri Jul 19, 2019 7:14 pm
by okjhum
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.
Re: How to calculate in a period: Years, Months and days.
Posted: Fri Jul 19, 2019 7:20 pm
by RayBir
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%!
Re: How to calculate in a period: Years, Months and days.
Posted: Fri Jul 19, 2019 7:22 pm
by 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.
Re: How to calculate in a period: Years, Months and days.
Posted: Fri Jul 19, 2019 7:28 pm
by Villeroy
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.