CODEWITHSUNDEEP
vbaexceldate
user5873603
user5873603

Reputation:

Excel VBA - Equivalent Prior Year Date

I have a worksheet formula which calculates the correct equivalent date in prior year. For example when reporting figures at work we don't compare 05/04/2016 against 05/04/2015 because the 5th in 2015 was a Sunday and this year it was a Tuesday. We therefore compare it against 07/04/2015.

Because of leap years it's also not always +2 days ahead (or -2 behind, whichever way you want to look at it).

I therefore created this worksheet formula which calculates the Week Start date of the same week in prior year, and adds weekday this year -1.

=(MAX(DATE(B2,1,1),DATE(B2,1,1)-WEEKDAY(DATE(B2,1,1),2)+(WEEKNUM(E3,2)-1)*7+1))+(WEEKDAY(E3,2)-1)

B2 = 2015

E3 = 05/04/2016

I want to convert this worksheet formula into a VBA Function so that I can pass any variable to it that I like and work out the equivalent date in prior year?

However, some of these functions don't seem to be available in VBA, I know Weekday is and I need to swap DATE for DateSerial. However it doesn't seem MAX is an available function in VBA? I also don't know about WEEKNUM?

If anyone could help convert this formula to a function I'd really appreciate it, this is what I have so far:

Function calcMTDDayDate(MTDDate As Date) As Date

End Function

Well.... I made a start at least...

Upvotes: 1

Views: 1000

Answers (1)

Phil
Phil

Reputation: 397

If you define a year as 52 weeks, rather than by days or months, then this makes the calculation much simpler, and you can use the DateAdd function.

eg:

Function calcMTDDayDate(MTDDate As Date) As Date
    calcMTDDayDate = DateAdd("ww", -52, MTDDate)
End Function

Upvotes: 1

Related Questions