validate business dates

[roosteroo]

hello all,

i need to validate a range of cells to test whether they are weekdays (or even better: and not holidays).

If any cell is found to be on a weekend, then stop and MsgBox this date is not valid.

thank you so much

 

[Jose_Chasez]

You could use Data Validation for this, and have an error window pop up if a "non-weekday" is input. The data validation formula would be as below (assumes date is input in cell A1)

=AND(WEEKDAY(A1)>1,WEEKDAY(A1)<7)

 

[Joe4]

There are various functions which may help with your tasks.

The WEEKDAY function gives numerical representation to each day of week. There are also other functions, such as WORKDAY and NETWORKDAY which can be used in calculations that can account for holidays.

Take a look at Excel's built-in help files for details and examples of how to use these functions.

 

[roosteroo]

it doesn't work, but this is what I have so far:

Sub Button3_Click()
    
    For I = 8 To 15
        If Weekday(Cells(I, 2).Value) = 1 Then
            MsgBox "invalid date"
        End If
    Next I
    
    For I = 8 To 15
        If Weekday(Cells(I, 2).Value) = 7 Then
            MsgBox "invalid date"
        End If
    Next I
    
End Sub

for some reason, every cell is an invalid date...

 

[Joe4]

You could use Data Validation for this, and have an error window pop up if a "non-weekday" is input. The data validation formula would be as below (assumes date is input in cell A1)

=AND(WEEKDAY(A1)>1,WEEKDAY(A1)<7)

Jose,
If you make use of the optional second argument of the WEEKDAY function (which tells what day to use for the first day of the week), you can make this a little shorter:
=WEEKDAY(A1,2) < 6

 

[Joe4]

for some reason, every cell is an invalid date...

Are you sure your dates are entered as "Date" and not "Text"?
What are some of your date values?
Are they entered in cells B8, B9, B10, etc?

 

[roosteroo]

Are you sure your dates are entered as "Date" and not "Text"?
What are some of your date values?
Are they entered in cells B8, B9, B10, etc?

yes, entered as date
from B8, B9 onwards,...

I have some empty cells though, how do I exclude those?
Also, how can I say "invalid date at date XXXX" ?
I tried MsgBox "invalid date at cell Cells(I, 2)" but doesn't work

<table width="75" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;width:56pt;font-size: 11.0pt;color:#2F2B20;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid #9CBEBD" width="75" height="20">23/09/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;font-size:11.0pt;color:#2F2B20; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #9CBEBD;background:#EBF1F1; mso-pattern:#EBF1F1 none" height="20">24/09/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;font-size:11.0pt;color:#2F2B20; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #9CBEBD" height="20">25/09/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;font-size:11.0pt;color:#2F2B20; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #9CBEBD;background:#EBF1F1; mso-pattern:#EBF1F1 none" height="20">26/09/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;font-size:11.0pt;color:#2F2B20; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #9CBEBD" height="20">27/09/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;font-size:11.0pt;color:#2F2B20; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #9CBEBD;background:#EBF1F1; mso-pattern:#EBF1F1 none" height="20">28/09/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;font-size:11.0pt;color:#2F2B20; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #9CBEBD" height="20">29/09/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;font-size:11.0pt;color:#2F2B20; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #9CBEBD;background:#EBF1F1; mso-pattern:#EBF1F1 none" height="20">30/09/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;font-size:11.0pt;color:#2F2B20; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #9CBEBD" height="20">01/10/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;font-size:11.0pt;color:#2F2B20; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #9CBEBD;background:#EBF1F1; mso-pattern:#EBF1F1 none" height="20">02/10/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;font-size:11.0pt;color:#2F2B20; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #9CBEBD" height="20">03/10/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;font-size:11.0pt;color:#2F2B20; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #9CBEBD;background:#EBF1F1; mso-pattern:#EBF1F1 none" height="20">04/10/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;font-size:11.0pt;color:#2F2B20; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border:.5pt solid #9CBEBD" height="20">05/10/2011</td> </tr> </tbody></table>

 

[scottylad2]

in a cell next to your dates, try =isnumber(A) and see if it's a TRUE

 

[Joe4]

Your code seems to work just fine for me.

Try this version of the code that will return more details when it finds an "invalid date", like what cell it is looking at and what the value is (maybe it is not running from where you intend it to run from):

Sub Button3_Click()
    
    For I = 8 To 15
        If Weekday(Cells(I, 2).Value, 2) > 6 Then
            MsgBox "Cell: " & Cells(I, 2).Address & vbCrLf & _
                    "Value: " & Cells(I, 2).Value & vbCrLf & _
                    "Is valid date?: " & IsDate(Cells(I, 2).Value) & vbCrLf & _
                    "Message: invalid date"
        End If
    Next I
    
End Sub

 

[roosteroo]

Joe4,
Your code works well thanks.

Though the first and only invalid date it picks up is 25/09/2011.
It should pick up 24/09/2011 as well. I don't understand where the code is faulty...

shouldn't the weekday comparison be with 5 instead of 6 ?
ie If Weekday(Cells(I, 2).Value, 2) > 5 Then