Excel 2003 - Input of dates in column...

  xphile 17:56 11 Dec 07
Locked

I have a column with a set of dates (in the future). I want this column to only accept dates that are weekdays Mon - Fri & to give an error message if the date is not a weekday - I haven't a clue if this is possible. Have tried looking under data/validation but I can't seem to do it - Would it need some sort of vba coding instead ? or am i missing something under data validation ?

  VoG II 20:24 11 Dec 07

Right click the sheet tab and select View Code. Copy and paste in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub 'Restrict to column A
Application.EnableEvents = False
If Not IsDate(Target.Value) Then
MsgBox prompt:="Not a date", Buttons:=vbCritical + vbOKOnly
Target.ClearContents
ElseIf WorksheetFunction.Weekday(Target, 2) > 5 Then
MsgBox prompt:="Not a weekday", Buttons:=vbExclamation + vbOKOnly
Target.ClearContents
End If
Application.EnableEvents = True
End Sub

Change the 1 in this line

If Target.Column <> 1 Then Exit Sub 'Restrict to column A

to the number of your column holding dates (1=A, 2=B etc.).

Close the code window then try entering some dates.

  Simsy 23:13 11 Dec 07

to detract from the excellent solution provided by VoG™, you could get the major part of this using validation, without VBA.


Select one of the cells where you want this to apply, then choose Data>Validation

(I'm using Excel 2000. I assume this will be the same for other versions!)

In the box that appears choose "Custom" in the Allow box. This will then invite you to enter a formula.
Enter the following, (substituting your selected cell for A1 in this example);

=weekday(A1,2)<6 (Lookup "Weekday" in the Help for an explanation)

Then hit the OK button.

Select the cell and copy it.
Select the range you want the validation to apply to, right click, choose "Paste Special" and choose validation.

That should do it.


What this wont do, that VoG™'s solution covers, is detect if a date is entered...

Eg, assuming the cell is formatted for some kind of date, and you intend to enter 5 May, but instead just enter the 5...

Because of the way Excel handles dates/times it will see this as Jan 5th 1900, (i.e. day number 5 in Excels counting system). This is a date, and because it wasn't a weekend, will be accepted.

If you need it to be foolproof then use VoG™'s method. If your security systems don't allow macros the data validation may offer a part solution.

Hope this helps.

Regards,

Simsy

  xphile 13:07 12 Dec 07

for your excellent reples. I had already entered VoG's response & seems to work a treat, before I saw the other one. But thank you very, very much for all responses. Ok probs being cheeky now, but when I press enter it moves to the next cell - naturally. When there's an error & I've tried to input a weekend date, is there a way to get it to stay at that cell (so I can have another go, so to speak), without moving back up? Ok that ones probs a bit lazy, just wondered if it wasn't too much code I could add it in.

  VoG II 13:19 12 Dec 07

OK, this will re-select the cell:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub 'Restrict to column A
Application.EnableEvents = False
If Not IsDate(Target.Value) Then
MsgBox prompt:="Not a date", Buttons:=vbCritical + vbOKOnly
Target.ClearContents
Target.Select
ElseIf WorksheetFunction.Weekday(Target, 2) > 5 Then
MsgBox prompt:="Not a weekday", Buttons:=vbExclamation + vbOKOnly
Target.ClearContents
Target.Select
End If
Application.EnableEvents = True
End Sub


Actually this code won't generate an error if 5 is emptied because 05/01/1900 is seen as a valid date. It would be possible to amend the code to only accept dates in the future to get around that problem.

  VoG II 13:21 12 Dec 07

'if 5 is entered'

  xphile 16:40 12 Dec 07

Thanks VoG that's great. Seasons greetings :)

This thread is now locked and can not be replied to.

Sniper Elite 4 review: Headshotting Nazis has never felt so good

1995-2015: How technology has changed the world in 20 years

The Best Design, Illustration, Animation and VFX Awards of 2017

WWDC 2017 dates: How to get WWDC 2017 tickets, when is WWDC 2017 and more details announced