CANADA VICTORIA DAY

[JUALOP60]

Hi,

how to calculate victoria day, with a formula in excel?

here are the details:

has to be Monday before May 25th.


Thank you

 

[glenntb]

This should work:

Function VictoriaDay(iYear As Integer) As Date
'
' Calculates the first Monday before 5/25 of the provided year
    Dim dtTest As Date
    
    dtTest = CDate("5/24/" & iYear)

    While Weekday(dtTest) <> 2  ' note: 2 = Monday
        dtTest = dtTest - 1
    Wend
    
    VictoriaDay = dtTest

End Function

 

[PHV]

Why not simply this ?

Function VictoriaDay(iYear As Integer) As Date
Dim dtTest As Date
dtTest = DateSerial(iYear, 5, 24)
VictoriaDay = dtTest - Weekday(dtTest, 2) + 1
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gruuuu]

Or, without VBA...
for current year

=DATEVALUE("5/24/"&YEAR(NOW()))-WEEKDAY(DATEVALUE("5/24/"&YEAR(NOW())))+1

for ambiguous year

=DATEVALUE("5/24/"&[i]year[/i])-WEEKDAY(DATEVALUE("5/24/"&[i]year[/i]))+1

 

[glenntb]

PHV: Nice! It actually lends itself to a non-VBA answer as well

[tt]
Year Formula Result
2010 =DATE(D3,5,24)-WEEKDAY(DATE(D3,5,24))+1 5/23/2010
2011 =DATE(D4,5,24)-WEEKDAY(DATE(D4,5,24))+1 5/22/2011
2012 =DATE(D3,5,24)-WEEKDAY(DATE(D3,5,24))+1 5/20/2012
[/tt]

 

[rustychef]

Insert in a MODULE, then when using the INSERT FUNCTION, you can find it listed under category USER DEFINED.

'-----------------------------------------------------------------------------------------
'-----------------------------------------------------------------------------------------
'-PROCEDURE: (Function) CalculateVictoriaDay -
'-----------------------------------------------------------------------------------------
'-PURPOSE: Calculate Victoria Day (Canada). Always occurs on the Monday prior to 25 May. -
'-----------------------------------------------------------------------------------------
'-INPUT: -
' Year_4Digits: Requires a 4-digit year. Can be a string or number. -
'-----------------------------------------------------------------------------------------
'-RETURN: Date. Date the holiday occurs for specified year. -
'-----------------------------------------------------------------------------------------
'--------------------------------------Created by Russell "Rusty" Adams II, 09 Jul 2010---
'-----------------------------------------------------------------------------------------
Function CalculateVictoriaDay(Year_4Digits As Variant) As Date
Dim dtStart As Date
Dim hWeekDay As Long
Dim hOffset As Long

'Date of reference.
dtStart = CDate("5/25/" & CStr(Year_4Digits))
'Day of the week reference falls on
hWeekDay = Weekday(dtStart)

'Seven is added to hWeekDay and then MOD'd by 7 so separate code
' isn't needed to handle instances when 25 May is a Sunday
hOffset = (((hWeekDay + 7) - vbMonday) Mod 7) * -1
CalculateVictoriaDay = DateAdd("d", hOffset, dtStart)

End Function 'CalculateVictoriaDay