validate business dates

roosteroo

roosteroo

New Member
Joined
Sep 19, 2011
Messages
10
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
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)
 
Upvote 0
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.
 
Upvote 0
it doesn't work, but this is what I have so far:

Code: 
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... :(
 
Upvote 0
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)
Click to expand...
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
 
Upvote 0
for some reason, every cell is an invalid date...
Click to expand...
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?
 
Upvote 0
Joe4 said:
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?
Click to expand...

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>
 
Last edited:
Upvote 0
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):
Code: 
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
 
Upvote 0
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Display Dates (minus weekends and holidays) Using TODAY() Function
Replies
5
Views
344
Asbestos_Jen
Asbestos_Jen
R
  • Solved
Overtime based on weekends AND holidays
2
Replies
12
Views
880
rockson
R
H
  • Question Question
VBA to protect/unprotect sheet...
Replies
6
Views
247
jkpieterse
jkpieterse
E
  • Solved
Conditional formatting
Replies
8
Views
162
eabaker64
E
D
  • Question Question
Modify a worksheet without registering it as a change
Replies
1
Views
105
Kaper
Kaper

Forum statistics

Threads
1,224,527
Messages
6,179,348
Members
452,907
Latest member
Roland Deschain

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top