[Solved] Date Calculation

Discuss the spreadsheet application
Post Reply
Papa_P
Posts: 3
Joined: Sun Jan 13, 2019 6:08 pm

[Solved] Date Calculation

Post by Papa_P »

I have a Date field formatted "YYYY-MM" representing the year and month members joined our Service Club. I now wish to create a Date Calculation Function in the member's "Seniority Pin" field that determines how many years of service they have contributed from joining until TODAY().

So far the member record on line 8 has the member "Seniority Pin" record field as: =YEARS(E8;TODAY();Type)

I'm not sure I'm doing this correctly or what "Type" the function is looking for.

Any assistance would be appreciated.

Thank you.
Last edited by Papa_P on Sun Jan 13, 2019 9:46 pm, edited 1 time in total.
OpenOffice 4.1.5
Windows 7 Home Premium Service Pack 1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Date Calculation

Post by Villeroy »

Code: Select all

=YEARS(E8;TODAY();Type)
first of all, test if E8 is a number: =ISNUMBER(E8)
If E8 is a number, the type parameter lets you specify if you just want the difference of year numbers as with the equivalent =YEAR(TODAY())-YEAR(E8)) or if you want the number of passed years as with the equivalent =YEAR(TODAY())-YEAR(E8))-(DATE(YEAR(TODAY());MONTH(E8);DAY(E8))>TODAY()) [diff of year numbers -1 if this year's E8 date is greater than today's date].

If E8 is not a number (if it is text) there is no way to calculate anything with E8. You may have some success with =YEARS(VALUE(E8);TODAY();Type)

The number format is pointless anyway since it does not affect the cell value. If E8 is a number, the number format must not (and does not) affect any calculation. If E8 is not a number, the number format does not even change the appearance of the cell E8.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Papa_P
Posts: 3
Joined: Sun Jan 13, 2019 6:08 pm

Re: Date Calculation

Post by Papa_P »

OK. The =ISNUMBER(E8) response was FALSE so I've split my DATE field into 2 columns to separate the year and month and then set the year column to NUMBER format "YYYY" at which time =ISNUMBER(E8) turned a TRUE.

However, =YEAR(TODAY())-E8 is returning a number that it is not correct.
OpenOffice 4.1.5
Windows 7 Home Premium Service Pack 1
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Date Calculation

Post by RoryOF »

Please tell us the values used and obtained, otherwise we are in the dark.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Date Calculation

Post by Zizi64 »

Please upload an ODF type sample file here.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Papa_P
Posts: 3
Joined: Sun Jan 13, 2019 6:08 pm

Re: Date Calculation

Post by Papa_P »

How do I mark this as solved.
OpenOffice 4.1.5
Windows 7 Home Premium Service Pack 1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Date Calculation

Post by Villeroy »

If you change your model from a string "2013-08" in E8 into 2 cells with two numbers 2013 in E8 and 8 in F8, the formula goes like this:

Code: Select all

=YEARS( DATE(E8;F8;1) ; TODAY() ; 0)
This calculates the years between today and the date which is specified by the year E8, month F8 and day #1
You may also enter a complete date 2013-8-1 in E8 and use =YEARS(E8;TODAY();1). The number format YYYY-MM will show this day number as "2013-08".

Dates are day numbers based on day zero 1899-12-30. The actual cell value of a number displayed as 2013-08-01 (first August 2013) is 41487.
=DAY(41487) => 1
=MONTH(41487) => 8
=YEAR(41487) => 2013
WEEKDAY(41487) => 5 [Thursday]
WEEKNUM(41487) => 31

[Tutorial] Ten concepts that every Calc user should know
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply