Calculate *exact* year difference between 2 dates

[cglisc]

Hello,

We are looking for a VBA function to calculate the exact difference in years between 2 dates. The problem at hand is to calculate the years of service of a employee with a company, which is the year difference between now and hire date.

DateDiff will not do, since it returns a long integer.
For example,
datediff("yyyy",cdate("2-10-2006"),cdate("31-10-2007"))
Returns 1, but is actually 1.03.

Is there a way to get this?

Thank you
Chris Lambrou

 

[strongm]

>is actually 1.03
Are you sure?

Perhaps you could check out the difference in weeks and then divide by 52

 

[Ed2020]

Something like this:

Public Function YearsService(ByVal dteStartDate As Date, ByVal dteEndDate As Date) As Double
    Dim intNoOfYears As Integer
    Dim intNoOfDays As Integer
    
    intNoOfDays = (dteEndDate - dteStartDate) Mod 365
    intNoOfYears = (dteEndDate - dteStartDate) \ 365

    YearsService = intNoOfYears + (intNoOfDays / 365)
End Function

Ed Metcalfe.

Please do not feed the trolls.....

 

[xlbo]

There is no possible way of extracting an "Exact" difference in years. Mainly because a year is not a standard unit of measure

It's like saying you want to know "exactly" how many months between x & y - well it all depends on the length of the month....

IMHO, the largest exactly measurable* unit of time is a week


*Where "exact" means "consistent"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

"IMHO, the largest exactly measurable* unit of time is a week"

But then, twice a year, here in the "colonies", a week is one hour shorter/longer due to "Daylight Saving Time" time shifts, thanks to Benjamin Franklin.

Well this past Sunday, the last Sunday of October, was to have been the day on which this shift would occur, to FALL back one hour, thus making the current week, one hour longer. But, alas, we must wait, yet another week (a standard week of 168 hours) for the time shift, when the week will be 169 hours. ;-)

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[WalkerEvans]

cglisc,

Exactly how precise does this need to be? I have a spreadsheet that does a lot of date calculations within the cells (not VBA); it's a complete pain in the rump, but it does work, figuring years, months, and days between two dates including adjustments for February (both Leap and non-Leap years). I can supply this but warn you ahead of time that it takes up a lot of cells, is really difficult to logic-trace, and will blowup in your face if even one character is typed wrong.

Geoff: As noted above, it IS possible to get precise, repeatable results down to the day - it just ain't easy!!



----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[xlbo]

Ok Skip - shall we have a discussion about atomic time measurement vs physical time measurement now.... ;-)

The point is that you can measure pretty well when an exact year has passed but it is very difficult to measure what proportion of a year has passed.

The statement: "This employee has had 1 full year if service" is correct

The statement: "This employee has had 1.03 years of service" may be correct (dependant on month length, time of year, whether it is a leap year etc) but is unlikely to be either "exactly" accurate or worthwhile.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

WalkerEvans

"As noted above, it IS possible to get precise, repeatable results down to the day - it just ain't easy!!"

According to YOUR set of rules, maybe. But it depends on how YOU define a MONTH or YEAR (really difficult to logic-trace). There is no ABSOLUTE definition, as there is for a WEEK, DST not withstanding. ;-)

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[AnotherHiggins]

No, it is not possible to get "exact" numbers of years in decimals, for the reasons that have been stated.

Sure, you can return years, months, days, hours, minutes and seconds between two dates/times. That's pretty straightforward.

But tenths or hundredths of a year? (That's what we're talking about here.) Well, define a year.

Here are some examples:
From
[tab]Oct 2 2006
Until
[tab]Oct 31 2007
Is
[tab]394 Days

If we assume a 365 day year, then
[tab]394/365 = 1.079452055

A year is really closer to 365.25 days. That gives us:
[tab]394/365.25 = 1.07871321

If we assume a 7 day week and 52 weeks in a year, then
[tab]394/7/52 = 1.082417582

If we assume a 30 day month and 12 months in a year, then
[tab]394/30/12 = 1.094444444

Decide on a measurement and stick with it. I'd personally suggest reporting years, months and days if you want precision.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Bong]

Surely, for the purposes of "length of service" a precision of months is enough, no?

print datediff("m",cdate("2-10-2006"),cdate("31-10-2007"))/12
 1.66666666666667

_________________
Bob Rashkin

 

[xlbo]

Yes I would've thought so. To be honest, I think most HR depts only use full years service so all you really need to know is how many FULL years a person has been employed - and that is easy to get

OP asked for exact years service however - this may be for some form of financial reward and if the multiplying factor is high, the difference between 1.03 years and 1.08 years could impact someone's pocket.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Its ALL in the DEFINITION that your company uses for Months and Years.

Bottome line: There can be MANY different "correct" solutions to the same question.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[AnotherHiggins]

[offtopic]

[!]WalkerEvans[/!]: Out of curiosity, what are you using to come up with years, months, days between dates? It shouldn't take up multiple cells or even be particularly lengthy.... Unless I'm just missing something right now.

You should start a new thread so we can help you come up with a simpler formula.

[/offtopic]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[WalkerEvans]

Skip,

I'll admit that these may be MY definitions but I'm pretty sure that most of the world agrees with me:

A MONTH is either that period from the first day to the last day of a universally accepted 1/12 division of a year (for example, 1 April - 30 April) or that period from an intermediate day in one of those divisions to the date immediately numerically preceding that same day in the division immediately following (for example, 15 March - 14 April). Alternately, it is the calculated number of 1/12 divisions once days have properly been accounted for, not to exceed 11 such divisions; any number greater than 11 roles over in the years calculation.

A YEAR is that period from any date in a calendar year to the date immediately numerically preceding that same date in the calendar year immediately following (for example, 14 December 2006 - 13 December 2007).

(Yes, various groups do use other calendars, primarily for religious [Hebrew] or cultural [Chinese] purposes; and yes, there are others. However, all of these still use the standard Julian calendar for all normal purposes.)

While I cannot say with finality that these are ABSOLUTES, in my experience the above are used almost exclusively around the world; thus, I tend to think of them as somewhat more than my own personal definitions.

Bong: ????? The dates given in cglisc's example yield one year, 29 days. I'm not sure how you arrived at 1.67 years.



----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[SkipVought]

"...most of the world agrees with me."

Really?

My Hire Date is November 29.

According to my company's rules, in some cases it is as if I were hired on November 1. I got vacation credit for Novenber, as if I had been there the entire month. So the DEFINITION of a month, in this case, is any day in a month results in an entire month.

So maybe "most of the world" with the exception of at least one large conglomerate in the USA.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[AnotherHiggins]

I really think the main issue is decimals. WalkerEvans stated that he calculates years, months and days between events. Yes, your definitions are pretty universal in that respect.

Though, as Skip points out, HR depts often only consider calendar months/calendar years for benefits[red]*[/red].

Still, even given WalkerEvans' definition of a year - how do you turn that into tenths of a year? It is simple to say "1 yr, 2 months, 3 days" and be correct. But see my post dated 29 Oct 07 10:07 for confusion that arises when trying to define a year to come up with decimals.

[red]*[/red]Case in point: At my company we get an extra week of vacation on our 3rd calendar year. I was hired on Sept 26. So after only 1 year, 3 months and 5 days, I was in my 3rd calendar year!

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[WalkerEvans]

John,

[offtopic]

While what I told Skip is correct, and the answers fairly straight-forward, what I have for internal use is anything but, and that is what I referred to as being convoluted; force of habit after a lot of years. What I have to use every day is a federal government payroll definition, where all months are 30 days long and all years are 360 days long.

The calculation of years, months, and days is no problem and involves only four cells, three of which refer to the fourth. That fourth cell contains IF tests to check to:

1) Make sure there is a valid date at the beginning of the row
2) Determine whether either the Start or Stop dates are in February
2a) If step 2 is Yes, determine whether or not a Leap Year is involved
3) Determine whether or not either date involves a month with 31 days

Once all of those checks are completed and the starting date subtracted from the ending date, a final answer is obtained which is then used to determine the cell values for years, months, and days. However, this is the easy part. I also have to calculate a "moveable" date, which is obtained by subtracting the second start date from the first end date, converting that to years, months, and days, and then determining the "moveable" date by adding the answer just obtained to the original start date. And this has to be repeated for each line; there can be as few as two lines, or as many as 40. It is this last step that takes up so many cells, and it is that calculation that always comes to mind whenever I refer to that sheet.

So, I mislead cglisc and everyone else when my autopilot kicked in and started talking about the long process, which wasn't even needed here; I apologize for the confusion I created. Now, if you think you might be able to come up with a shorter way to get that "moveable" date, I'd be happy to send you a copy of my spreadsheet. Since it resides on a government computer with pretty good safeguards it will not be as simple as just posting a link, but I can work around that. If you have a big bottle of aspirin (and maybe some 20 year old Scotch) you're welcome to play with it but be warned: It has made some pretty good VBA people breakdown and start babbling.

[/offtopic]



----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[Bong]

...where all months are 30 days long and all years are 360 days long

and [π][≡]3

_________________
Bob Rashkin

 

[AnotherHiggins]

[offtopic]

where all months are 30 days long and all years are 360 days long.

Well that's different.

I was thinking about standard calendar operations which could easily be handled as follows:[tt]
[tab]Years: =DATEDIF(A2,B2,"y")
[tab]Months: =DATEDIF(A2,B2,"ym")
[tab]Days: =DATEDIF(A2,B2,"md")[/tt]
where A2 contains the start date and B2 contains the end date.

Or, all together:
[tab][COLOR=blue white]=DATEDIF(A2,B2,"y") & " Years, " & DATEDIF(A2,B2,"ym") & " Months, " & DATEDIF(A2,B2,"md") & " Days"[/color]

Looking over your post, I don't understand what the "second start date" is. But I'm willing to bet (I'll let you know how much when I've seen the entire problem ) that if you start a thread in forum68 and post some sample data along with the formulas you currently use and the results you expect to get, someone will be able to help clean it up. I've seen some solutions that leave my head spinning.

[/offtopic]


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[macropod]

If this is for Excel, you could use the standard worksheet functions. For example:

If the start date is in A1 and the end date is in A2, the following formula expresses the intervening period in years, months and days:
=DATEDIF(A1,A2,"y")&" years, "&DATEDIF(A1,A2,"ym")&" months & "&DATEDIF(A1,A2,"md")&" days."
If you need to include both the starting an end dates in the calculation, you can use the following variation to the above formula:
=DATEDIF(A1-1,A2,"y")&" years, "&DATEDIF(A1-1,A2,"ym")&" months & "&DATEDIF(A1-1,A2,"md")&" days."

As previously pointed out, there is no standard way to convert the residual month & day components to a simple fraction.

That's about as 'exact' as you can get, unless perhaps you want to use average calendar years (ie 1 year = 365.2425 days when you take account of all the leap year possibilities) to express the remainders as decimals.

Cheers

[MS MVP - Word]