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

Discuss the spreadsheet application

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

Postby gerard24 » Thu Jul 18, 2019 5:53 pm

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.2.8 on Windows 10
gerard24
Volunteer
 
Posts: 952
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

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

Postby RayBir » Thu Jul 18, 2019 7:12 pm

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

Postby okjhum » Fri Jul 19, 2019 7:14 pm

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.

Postby RayBir » Fri Jul 19, 2019 7:20 pm

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

Postby RoryOF » Fri Jul 19, 2019 7:22 pm

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.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29896
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

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

Postby Villeroy » Fri Jul 19, 2019 7:28 pm

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.x
User avatar
Villeroy
Volunteer
 
Posts: 27380
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Previous

Return to Calc

Who is online

Users browsing this forum: MSN [Bot] and 28 guests