[Solved] Date Calculation

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

[Villeroy]

Code: Select all   Expand viewCollapse view

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

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

[RoryOF]

Please tell us the values used and obtained, otherwise we are in the dark.

[Zizi64]

Please upload an ODF type sample file here.

[Papa_P]

How do I mark this as solved.

[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   Expand viewCollapse view

=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