Dealing with clock changes in MS Excel.

[tell-me-more]

I'd like to get Excel to do a calculation only if the date is the last Sunday in October (for the extra hour in that day).

I'd like this to be coded rather than me having to identify the last Sunday in October for each year.

Eventually I'd also like to omit the calculation for the 24th hour of the last Sunday in March.

Any suggestions for an elegant way of doing it? I was thinking of using the IF function, but not sure how I would code "the last Sunday in October" yet.

[jordi2k]

=DATE(YEAR(A1),MONTH(A1)+1,0)
Where cell "A1" contains a date will return the Last day of that month....

=EOMONTH(A1,0)
will do the same but requires the "Analysis Toolpak" installed on the machine (Tools > Add-Ins)

=WEEKDAY(A1,2)
Will return a number 1 - 7 where Monday = 1 and Sunday = 7

[jordi2k]

Public Function Calc_Date() As Boolean

Dim LValue As Integer
myDay = 24
myMonth = 10
myYear = Year(Now())

Do
myDay = myDay + 1
LValue = DatePart("w", myDay & "/" & myMonth & "/" & myYear, vbMonday, vbFirstJan1)
Loop Until LValue = 7

If Trim$(Date) = myDay & "/" & myMonth & "/" & myYear Then
Calc_Date = True
Else
Calc_Date = False
End If

End Function
Public Function Calc_Hour() As Boolean

Dim LValue As Integer

myDay = 24
myMonth = 3
myYear = Year(Now())

If Len(myMonth) = 1 Then myMonth = "0" & myMonth

Do
myDay = myDay + 1
LValue = DatePart("w", myDay & "/" & myMonth & "/" & myYear, vbMonday, vbFirstJan1)
Loop Until LValue = 7

myHour = DatePart("h", myDay & "/" & myMonth & "/" & myYear, vbMonday, vbFirstJan1)

If Trim$(Date) = myDay & "/" & myMonth & "/" & myYear And myHour = 0 Then
Calc_Hour = True
Else
Calc_Hour = False
End If

End Function

[jordi2k]

^^^^^^^^^^^^^^^^^^^^^^

If you paste the above into an Excel VBA Module you will see two functions...

Calc_Date will return TRUE if the date today is the last Sunday in October, automatically worked out..

Calc_Hour will return TRUE if the hour now is 0 (or past midnight) and the current date is the last Sunday in March... again calc'ed automatically...

This code was made from scratch and not ripped from a site.. so if you use it, a thanks wouldnt go amiss.

[tell-me-more]

Thank you.