CODEWITHSUNDEEP
vba
Sam
Sam

Reputation: 736

DateDiff not working when finding amount of years between two dates

I've got the following formula

  number_of_years = DateDiff("yyyy", Date_days_back, Current_Date)

where Date_days_back = 17/10/2020 Current_Date = 02/08/2021

for some reason this returns "1" when there aren't 365 days apart between these two dates, is something wrong with my formula or is DateDiff calculating this wrong? I would like to get "0" returned as there aren't enough amount of days between these two dates for a year to have passed between the two dates.

Comment: As Scott Stated, if I'm looking from December 31st 2020 and 1st of January 2021, then this function will count this as a year has passed. is there another function that calculates if a years has passed in between two dates where it doesn't do what DateDiff does and instead counts if 365 have passed in between the two dates?

Upvotes: 1

Views: 568

Answers (2)

Алексей Р
Алексей Р

Reputation: 7627

Try to use YearFrac(). By setting the third argument to 1, the function uses the actual number of days in years (taking into account leap years) in its calculations:

Int(WorksheetFunction.YearFrac("2020-12-31", "2021-12-31", 1))

Comparison:

Sub Example()
    start_date = CDate("2020-12-31")
    end_date = CDate("2021-01-01")
    
    Debug.Print Int(WorksheetFunction.YearFrac(start_date, end_date, 1))
    ' prints 0
    Debug.Print DateDiff("yyyy", start_date, end_date)
    ' prints 1
End Sub

Upvotes: 3

Deepstop
Deepstop

Reputation: 3807

This seems to work

function yeardiff(d1 as date, d2 as date) as integer
     yeardiff = datediff("yyyy", d1, dateadd("yyyy", -1, dateadd("d", 1, d2)))
end function

Tests

print yeardiff("2020-12-31", "2021-12-30")
0

print yeardiff("2020-12-31", "2021-12-31")
1

Of course it will also give,

print yeardiff("2020-12-31", "2020-12-30")
-1

And if something different is desired then a conditional would be needed.

Upvotes: 1

Related Questions