How to determine if last day of month falls on weekend

[ibadk]

Hey Guys,

I want to write a macro that looks at a cell in which a date is input in the format mm/dd/yyyy (for example a cell titled "Date"), and if this date is the last date of the month and it falls on a weekend (eg. July 31/2011 falls on a Sunday) then I want it to return a msg box informing the user that the last day of the month is falling on a weekend. How would I write such a code. I'm so lost. All I know right now is that i can utilize the EOmonth function, and perhaps the weekday function, but I don't know how to write a functioning code that utilizes these functions. Any help, or an example of how I would go about this would be appreciated. Thanks guys.

 

[Weaver]

Use

=WEEKDAY(A1,2)

and if the result is >5 it's a weekend

HTH

PS if you really need a message box, you might have to resort to VB code

 

[ibadk]

Actually that's what I wanted to do, write an actual vba code that performs the above mentioned routine. Basically I already have a code that runs a certain report. I want to insert a code before my actual subroutine that would perform the check that I mentioned earlier.

 

[Phanmore]

Here's some code I use to check weekend dates. It applies to all weekends though, not just the last day of the month, but might point you in the right direction.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Set Rng = Range("A1")
If Not Intersect(Target, Rng) Is Nothing Then
WD = Application.WorksheetFunction.Weekday(Rng.Value)
If WD = 1 Or WD = 7 Then MsgBox ("Please ammend date, weekday only")
Else: Exit Sub
End If
End Sub

 

[JoeMo]

Try this:

Sub CheckDate()
Dim rng As Range, dte As Double

Set rng = ActiveSheet.Range("A1")   'change cell address to suit
If IsDate(rng.Value) Then
    dte = rng.Value
    If dte <> WorksheetFunction.EoMonth(rng, 0) Then
        Exit Sub
    ElseIf WorksheetFunction.Weekday(rng) = 1 Or _
        WorksheetFunction.Weekday(rng) = 7 Then MsgBox _
        "date is last day of month & falls on a weekend"
    End If
End If
End Sub

 

[ibadk]

Try this:

Sub CheckDate()
Dim rng As Range, dte As Double

Set rng = ActiveSheet.Range("A1")   'change cell address to suit
If IsDate(rng.Value) Then
    dte = rng.Value
    If dte <> WorksheetFunction.EoMonth(rng, 0) Then
        Exit Sub
    ElseIf WorksheetFunction.Weekday(rng) = 1 Or _
        WorksheetFunction.Weekday(rng) = 7 Then MsgBox _
        "date is last day of month & falls on a weekend"
    End If
End If
End Sub

That worked perfectly. Thank you so much. I've spent the last day trying to figure this out.