## [Solved] Date Calculation

### [Solved] Date Calculation

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
Papa_P

Posts: 3
Joined: Sun Jan 13, 2019 6:08 pm

### Re: Date Calculation

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x

Villeroy
Volunteer

Posts: 26635
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Date Calculation

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
Papa_P

Posts: 3
Joined: Sun Jan 13, 2019 6:08 pm

### Re: Date Calculation

Please tell us the values used and obtained, otherwise we are in the dark.
Apache OpenOffice 4.1.6 on Xubuntu 18.04.2 (mostly 64 bit version) and very infrequently on Win2K/XP

RoryOF
Moderator

Posts: 28784
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

### Re: Date Calculation

Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
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.

Zizi64
Volunteer

Posts: 7912
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: Date Calculation

How do I mark this as solved.
OpenOffice 4.1.5
Windows 7 Home Premium Service Pack 1
Papa_P

Posts: 3
Joined: Sun Jan 13, 2019 6:08 pm

### Re: Date Calculation

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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x

Villeroy
Volunteer

Posts: 26635
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany