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.
[Solved] Date Calculation
[Solved] Date Calculation
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
Windows 7 Home Premium Service Pack 1
Re: Date Calculation
Code: Select all
=YEARS(E8;TODAY();Type)
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
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.
However, =YEAR(TODAY())-E8 is returning a number that it is not correct.
OpenOffice 4.1.5
Windows 7 Home Premium Service Pack 1
Windows 7 Home Premium Service Pack 1
Re: Date Calculation
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
Re: Date Calculation
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.
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.
Re: Date Calculation
How do I mark this as solved.
OpenOffice 4.1.5
Windows 7 Home Premium Service Pack 1
Windows 7 Home Premium Service Pack 1
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:
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
Code: Select all
=YEARS( DATE(E8;F8;1) ; TODAY() ; 0)
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice