Data Validation - Workdays

[Fear Naught]

I want to ensure that users can only enter a working day (Monday to Friday) in a column. I know I'll need a custom validation formula but have not been able to work it out.

Any ideas??

Thanks.

 

[Brianwarnock]

If you mean the texts Monday to Friday then this could be entered from a list, this is explained in Help.

If however you mean a date that is a monday to friday then you could try useing a formula in data validation

Brian

 

[boblarson]

I was wondering that as well. Do you mean entering the day name or a DATE which can only be anything BUT Saturday or Sunday. For that we could use a validation rule of

Format([YourFieldName], "dddd") Not In("Saturday", "Sunday")

 

[chergh]

The data validation formula is:

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

not sure how you would do it for an entire column without it taking ages, I would use the change event personally.

 

[Fear Naught]

The data validation formula is:

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

not sure how you would do it for an entire column without it taking ages, I would use the change event personally.

Thanks everyone. I did it exactly as you said "chergh" but found the solution on www.contextures.com at http://www.contextures.com/xlDataVal07.html#Weekend before you posted.

Tim

 

[boblarson]

I was wondering that as well. Do you mean entering the day name or a DATE which can only be anything BUT Saturday or Sunday. For that we could use a validation rule of

Format([YourFieldName], "dddd") Not In("Saturday", "Sunday")

DARN!!! I keep missing when it is an Excel question and not an Access one, even though I TRY to pay attention to what category it is in. Sorry for the wrong info.