[Solved] Date Calculation

Discuss the spreadsheet application

[Solved] Date Calculation

Postby Papa_P » Sun Jan 13, 2019 6:34 pm

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

Postby Villeroy » Sun Jan 13, 2019 7:00 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27381
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Date Calculation

Postby Papa_P » Sun Jan 13, 2019 8:29 pm

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

Postby RoryOF » Sun Jan 13, 2019 8:31 pm

Please tell us the values used and obtained, otherwise we are in the dark.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29899
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Date Calculation

Postby Zizi64 » Sun Jan 13, 2019 8:40 pm

Please upload an ODF type sample file here.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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.
User avatar
Zizi64
Volunteer
 
Posts: 8542
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Date Calculation

Postby Papa_P » Sun Jan 13, 2019 8:46 pm

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

Postby Villeroy » Sun Jan 13, 2019 9:28 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27381
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 42 guests