Check IF Input Date is Weekday, THEN ... ELSE ...

[JohnZ1156]

I'm looking for a simply macro to test if an Input date is a weekday or not.

Something Like:
If (Input date) is a Weekday, THEN

'Run this code

ELSE

'Run this code
End If

 

[Cubist]

Try:

Sub CheckWeekday()
    Dim inputDate As Date

    inputDate = DateValue("2024-04-24") 
    
    If Weekday(inputDate, vbMonday) <= 5 Then
        ' Run code for weekday
        MsgBox "It's a weekday!"
    Else
        MsgBox "It's not a weekday."
    End If
End Sub

 

[JohnZ1156]

I made some minor changes, but you definitely got me on the right track !!!
Thank you.

Sub CheckWeekday()
    Dim inputDate As Date

    inputDate = InputBox("Please enter the Date.", "Old Date", Default, XPos:=2880, YPos:=5760)

    If Weekday(inputDate, vbMonday) <= 5 Then
        ' Run code for weekday
        MsgBox "It's a weekday!"
    Else
        MsgBox "It's not a weekday."
    End If
End Sub

 

[Jaafar Tribak]

I made some minor changes, but you definitely got me on the right track !!!
Thank you.

Sub CheckWeekday()
    Dim inputDate As Date

    inputDate = InputBox("Please enter the Date.", "Old Date", Default, XPos:=2880, YPos:=5760)

    If Weekday(inputDate, vbMonday) <= 5 Then
        ' Run code for weekday
        MsgBox "It's a weekday!"
    Else
        MsgBox "It's not a weekday."
    End If
End Sub

I would add some validation in case the input is not a date or the users click Cancel :

Sub CheckWeekday()
    Dim inputDate As Variant

    inputDate = InputBox("Please enter the Date.", "Old Date", Date, XPos:=2880, YPos:=5760)
    
    If Not IsDate(inputDate) Then Exit Sub

    If Weekday(inputDate, vbMonday) <= 5 Then
        ' Run code for weekday
        MsgBox "It's a weekday!"
    Else
        MsgBox "It's not a weekday."
    End If
    
End Sub

 

[Joe4]

Or better yet...

Sub CheckWeekday()
    Dim inputDate As Variant

    inputDate = InputBox("Please enter the Date.", "Old Date", Date, XPos:=2880, YPos:=5760)
   
    If Not IsDate(inputDate) Then
        MsgBox "That is not a valid date!", vbOKOnly, "TRY AGAIN!"
        Exit Sub
    End If

    If Weekday(inputDate, vbMonday) <= 5 Then
        ' Run code for weekday
        MsgBox "It's a weekday!"
    Else
        MsgBox "It's not a weekday."
    End If
   
End Sub

Otherwise it is not easy to differentiate between an invalid date entry, and the code just not working properly since it isn't giving the user any indication of what is happening.

 

[Jaafar Tribak]

Or better yet...

Sub CheckWeekday()
    Dim inputDate As Variant

    inputDate = InputBox("Please enter the Date.", "Old Date", Date, XPos:=2880, YPos:=5760)
 
    If Not IsDate(inputDate) Then
        MsgBox "That is not a valid date!", vbOKOnly, "TRY AGAIN!"
        Exit Sub
    End If

    If Weekday(inputDate, vbMonday) <= 5 Then
        ' Run code for weekday
        MsgBox "It's a weekday!"
    Else
        MsgBox "It's not a weekday."
    End If
 
End Sub

Otherwise it is not easy to differentiate between an invalid date entry, and the code just not working properly since it isn't giving the user any indication of what is happening.

I would also check if the user cancelled the inputbox and skip the MsgBox as follows:

Sub CheckWeekday()
    Dim inputDate As Variant

    inputDate = InputBox("Please enter the Date.", "Old Date", Date, XPos:=2880, YPos:=5760)
  
    If StrPtr(inputDate) = 0 Then Exit Sub
  
    If Not IsDate(inputDate) Then
        MsgBox "That is not a valid date!", vbOKOnly, "TRY AGAIN!"
        Exit Sub
    End If

    If Weekday(inputDate, vbMonday) <= 5 Then
        ' Run code for weekday
        MsgBox "It's a weekday!"
    Else
        MsgBox "It's not a weekday."
    End If
  
End Sub

 

[JohnZ1156]

Thanks Joe4,
Cubist's suggestion worked fine, but I did decided to include your error checking.

 

[Joe4]

Note that Jafaar's last post goes one step further than mine!

 

[JohnZ1156]

Thank you all !!!

 

[JohnZ1156]

Hey folks, I really appreciate all of your help, but I have one quick question.
Here is the final macro script after I made a few minor changes to test it out.
My question is: If I'm entering the date as MM/DD/YYYY, why does it allow me to enter 13/1/2024 as a date? There is no 13th month?
It's accepting this date and telling me that 13/1/2024 is not a weekday!
Shouldn't it return that 13/1/2024 is not a valid date?

Sub CheckWeekday3()
    Dim inputDate As Variant

    inputDate = InputBox("Please enter the Date.", "Old Date", Date)
  
    If StrPtr(inputDate) = 0 Then Exit Sub
  
    If Not IsDate(inputDate) Then
        MsgBox "That is not a valid date!", vbOKOnly, "TRY AGAIN!"
        Exit Sub
    End If

    If Weekday(inputDate, vbMonday) <= 5 Then
        ' Run code for weekday
        Worksheets("Sheet1").Range("H3").Value = inputDate
        MsgBox "It's a weekday! - " & inputDate
    Else
        ' Run code for weekend
        Worksheets("Sheet1").Range("H3").Value = inputDate
        MsgBox "It's not a weekday! - " & inputDate
    End If
  
End Sub