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

 

[AnotherHiggins]

=DATEDIF(A2,B2,"y") & " Years, " & DATEDIF(A2,B2,"ym") & " Months, " & DATEDIF(A2,B2,"md") & " Days"

=DATEDIF(A1,A2,"y")&" years, "&DATEDIF(A1,A2,"ym")&" months & "&DATEDIF(A1,A2,"md")&" days."

Am I missing something, or are those the same other than your addition of a period (full stop)?

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

Hi John,

Your's may be the same, but mine aren't. So, yes, you're missing something (hint: -1)<g>

Cheers

[MS MVP - Word]

 

[xlbo]

<offtopic>
WalkerEvans - have you seen the Days360 function in excel? Given your scenario, it may help (or just make things even more confusing!)

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

 

[cglisc]

Thank you all for the answers, although down the road this became an excel thread.

What I am actually looking for is something similar to oracle's months_between function, and then devide by 12.

If you do this in in oracle sql prompt

select months_between( to_date('1-11-2006','dd-mm-yyyy')  ,to_date('2-10-2006','dd-mm-yyyy') ) from dual

you will get 0.967741935483871. Then you devide by 12, round it up and then voila! Exact number of years between dates.

The closest answer to what I need is Ed2020's but that always takes 365 days in a year.

Thank you all

 

[Ed2020]

Simply add a check to see whether the year is leap year and use 366 if it is:

Function IsLeapYear() as boolean
   If (Year(Date) Mod 4) = 0 Then
      IsLeapYear=True
   Else
      IsLeapYear=False
   End If
End Function

Ed Metcalfe.

Please do not feed the trolls.....

 

[Ed2020]

Sorry, the code in my last post was incorrect. This should be correct:

Function IsLeapYear(ByVal intYear As Integer) As Boolean
    If intYear Mod 400 = 0 Then
        IsLeapYear = True
    ElseIf intYear Mod 100 = 0 Then
        IsLeapYear = False
    ElseIf intYear Mod 4 = 0 Then
        IsLeapYear = True
    Else
        IsLeapYear = False
   End If
End Function

Please do not feed the trolls.....

 

[macropod]

Hi cglisc,

For Oracle to return a value like 0.967741935483871, it would appear to be using something akin to Microsoft Excel's DAYS360 function, since there's no way the difference between those two dates can fall below 0.9836 months using 356 to 366 days for the year. Using Excel's DAYS360 function thus:
=DAYS360(A2,A1)/DAYS360(A2,DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)))*12
returns 0.96666666666667.

Cheers

[MS MVP - Word]

 

[AnotherHiggins]

>Exact number of years between dates.

*sigh*

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

Without trying to post all of the formulas in all of the cells let me see if I can illustrate what I'm dealing with by showing you that portion of the spreadsheet that is visible to the user:


Beginning Ending Years Months Days PBD

01/01/04 02/28/05 1 2 01/01/04
03/17/07 04/26/07 1 10 01/17/06
09/01/07 11/01/07 2 1 05/21/06

The column labeled PBD is initially set equal to the Beginning Date of the first row. After that the PBD is derived by taking the Beginning Date in the next row and subtracting the PBD from the previous row to arrive at the interval in years, months, and days. This is then added to the previous PBD to determine the current PBD. Since one of the constraints is the 30 day month, the formulae have to be adjusted to subtract 1 day from any 31 day month, and to do all of the convoluted logic necessary to determine how many days are "actually in" February. For example, in 2006 (a non-leap year) Feb had 28 days; therefore, if the entire month was served credit is established for 30 days - if entitlement terminated on the 27th, even though there is only one day lost by the calendar, only 27 days accrue for credit. You can see how much fun this is going to be for a leap year.

I am using DAYS360 in some of these calculations, but by itself it will not yield a consistently correct answer for the PBD date. I shot an e-mail the boys in Redmond, WA a couple of years ago, explaining the problem and asking if there was some built-in that I was missing that would help; included was an unprotected copy of my spreadsheet so that they could see what was needed and what I had done to get it to work. The e-mail was acknowledged as received and nothing else has been heard from that quarter since.

I would be happy to set a link to a slightly modified copy of this spreadsheet (certain identifying items must be removed) but it will have to be done later from my home computer; if I try to link with this one the IT and Legal staffers will be on me like white on rice.

[/offtopic]



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