Page 1 of 1
[Solved] Date Calculation
Posted: Sun Jan 13, 2019 6:34 pm
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.
Re: Date Calculation
Posted: Sun Jan 13, 2019 7:00 pm
by Villeroy
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.
Re: Date Calculation
Posted: Sun Jan 13, 2019 8:29 pm
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.
Re: Date Calculation
Posted: Sun Jan 13, 2019 8:31 pm
by RoryOF
Please tell us the values used and obtained, otherwise we are in the dark.
Re: Date Calculation
Posted: Sun Jan 13, 2019 8:40 pm
by Zizi64
Please upload an ODF type sample file here.
Re: Date Calculation
Posted: Sun Jan 13, 2019 8:46 pm
by Papa_P
How do I mark this as solved.
Re: Date Calculation
Posted: Sun Jan 13, 2019 9:28 pm
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