Sub CheckWeekday3()
Dim inputDate As Variant
Dim arr() As String
inputDate = InputBox("Please enter the Date in mm/dd/yyyy format.", "Old Date", Date)
If StrPtr(inputDate) = 0 Then Exit Sub
' Check first part of date of entry (month)
arr = Split(inputDate, "/")
If arr(0) > 12 Then
MsgBox "Invalid month entry of " & arr(0), vbOKOnly, "TRY AGAIN!"
Exit Sub
End If
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
Sub CheckWeekday3()
Dim inputDate As Variant
Dim delim As String
Dim arr() As String
inputDate = InputBox("Please enter the Date", "Old Date", Date)
If StrPtr(inputDate) = 0 Then Exit Sub
' Find what is being used as the date delimiter
If InStr(1, inputDate, "/") > 0 Then
delim = "/"
Else
If InStr(1, inputDate, "-") > 0 Then
delim = "-"
End If
End If
' Check to see if they used "/" or "-" as a delimiter
If delim = "" Then
MsgBox "Please enter date in mm/dd/yyyy or mm-dd-yyyy format", vbOKOnly, "TRY AGAIN!"
Exit Sub
End If
' Check first part of date of entry (month)
arr = Split(inputDate, delim)
If arr(0) > 12 Then
MsgBox "Invalid month entry of " & arr(0), vbOKOnly, "TRY AGAIN!"
Exit Sub
End If
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
You are welcome.Great ! Thank you.
It's just that I have different people entering the data.
inputDate = InputBox("Please enter the Date in mm/dd/yyyy format.", "Old Date", Date)
Because 13/1/2024 is still a valid date. The vba IsDate function sees it as the 13th of Jan 2024.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?
Option Explicit
#If VBA7 Then
Private Declare PtrSafe Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String, ByVal cchData As Long) As Long
Private Declare PtrSafe Function GetUserDefaultLCID Lib "kernel32" () As Long
#Else
Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String, ByVal cchData As Long) As Long
Private Declare Function GetUserDefaultLCID Lib "kernel32" () As Long
#End If
Private Function GetSystemDateFormat() As String
Const LOCALE_USER_DEFAULT = &H400, LOCALE_SSHORTDATE = &H1F
Dim sBuffer As String * 128&, lRet As Long
lRet = GetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_SSHORTDATE, sBuffer, 128&)
If lRet Then
GetSystemDateFormat = Left$(sBuffer, lRet - 1&)
End If
End Function
Sub CheckWeekday4()
Dim inputDate As Variant, strFormat As String
inputDate = InputBox("Please enter the Date.", "Old Date", Date)
If StrPtr(inputDate) = 0 Then Exit Sub
If Not IsDate(inputDate) Then
GoTo ErrHandler
End If
inputDate = Replace(inputDate, "-", "/")
inputDate = Right("00" & Split(inputDate, "/")(0), 2) & "/" & _
Right("00" & Split(inputDate, "/")(1), 2) & "/" & _
Split(inputDate, "/")(2)
strFormat = GetSystemDateFormat
If UCase(Left(strFormat, 1)) = "D" Then
If Mid(inputDate, 4, 2) > 12 Then
GoTo ErrHandler
End If
Else
If UCase(Mid(inputDate, 1, 2)) > 12 Then
GoTo ErrHandler
End If
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
Exit Sub
ErrHandler:
MsgBox "That is not a valid date!", vbOKOnly, "TRY AGAIN!"
End Sub
Option Explicit
#If VBA7 Then
Private Declare PtrSafe Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String, ByVal cchData As Long) As Long
#Else
Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String, ByVal cchData As Long) As Long
#End If
Private Function GetSystemDateFormat() As String
Const LOCALE_USER_DEFAULT = &H400, LOCALE_SSHORTDATE = &H1F
Dim sBuffer As String * 128&, lRet As Long
lRet = GetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_SSHORTDATE, sBuffer, 128&)
If lRet Then
GetSystemDateFormat = Left$(sBuffer, lRet - 1&)
End If
End Function
Sub CheckWeekday()
Dim inputDate As Variant, strFormat As String
inputDate = InputBox("Please enter the Date.", "Old Date", Date)
If StrPtr(inputDate) = 0 Then Exit Sub
strFormat = GetSystemDateFormat
If Not IsDate(inputDate) Then
GoTo ErrHandler
End If
inputDate = Replace(inputDate, "-", "/")
If InStr(inputDate, "/") = 0 Then GoTo ErrHandler
inputDate = Right("00" & Split(inputDate, "/")(0), 2) & "/" & _
Right("00" & Split(inputDate, "/")(1), 2) & "/" & _
Split(inputDate, "/")(2)
If UCase(Left(strFormat, 1)) = "D" Then
If Mid(inputDate, 4, 2) > 12 Then
GoTo ErrHandler
End If
Else
If UCase(Mid(inputDate, 1, 2)) > 12 Then
GoTo ErrHandler
End If
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
Exit Sub
ErrHandler:
MsgBox "Please enter date in " & LCase(strFormat) & " format", vbOKOnly, "TRY AGAIN!"
End Sub