multiple validation rules

**Mayrie** · 09-22-2009, 02:42 AM

In Excel 2007, I am trying to apply a validation rule for a cell with multiple conditions. I am selecting custom validation in the validation selection box and my formula is:

=IF(OR(B2="off", B2="not available", B2="request off"), " ")

I want the cell to be blank if B2 has the value "off", "not available" or "request off", and for the cell to be allowed to be any other time value if B2 is not one of the three criteria. This is an employee schedule and I am trying to set it up so that if a person is supposed to be off, the scheduler is not able to insert a shift for them.

What am I doing wrong with the above formula? Should I be doing something else instead of this formula?

Thanks!

Mayrie

**DonkeyOte** · 09-22-2009, 02:49 AM

If we assume the start shift value is to be entered into C2 (ie that is the cell to which we are applying validation) then perhaps:

Please Login or Register  to view this content.

should only permit time values where B2 not one of those three values, of course this can be circumvented by not having these options in B2, entering time in C2 and then reverting B2 to one of these conditions - this may or may not be an issue... if it is then you will need to use VBA I'm afraid.

**Mayrie** · 09-22-2009, 03:19 AM

For B2 I have a drop down list of job codes to choose from, I also have the off, request off, and not available options in the list. Scheduling I first assign the job code in b2 then schedule the start and end times in c2 and d2 respectively.

I input the and function you provide and it seems to be working great so far. Thanks so much for the quick reply.

**DonkeyOte** · 09-22-2009, 03:21 AM

OK that's good... that's the setup I envisaged, the validation rule for C2 can be copied to D2 also without need for alteration.
(though I should add that is making the assumption that you are only entering time values and not date time (the use >= 0 and <1 limit, time in XL is decimal)

**Mayrie** · 09-22-2009, 03:49 AM

I am only entering times, except when I have an employee that is working past midnight. In order for the hours to calculate correctly I have to input the start times like this:

Start Time End Time
9/18/09 6:00 pm 9/19/09 12:15 am

Will I be able to do this with the function you provided?

**DonkeyOte** · 09-22-2009, 04:01 AM

No it wouldn't I'm afraid but to go back one step:

I am only entering times, except when I have an employee that is working past midnight.

You can use MOD to calculate the hours using just time assuming no shift ever exceeds 24 hours in length, eg:

Please Login or Register  to view this content.

will correctly calculate the hours even if shift crosses midnight.. and so using this approach you need only enter time values into both C & D cells (6:00 pm / 12:15 am)

**Mayrie** · 09-23-2009, 01:34 PM

This works perfect... and it will save me time by not having to enter the dates. Thanks so much!

**CzR852** · 09-07-2012, 12:46 PM

I got to this page through Google search. I am trying to do a validation against a date, and would appreciate help with these questions (Novice Excel user

):
Can a cell have a calendar button - for user to choose a date, and also have data validation? (so far I've played with validation, haven't found how to include calendar)
I would like to validate that the chosen date is at least TODAY+14, but also is a Saturday - is there any validation which will provide this? Thanks for any assistance. CzR

**Cutter** · 09-07-2012, 08:50 PM

Hello CzR852, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.