multiple validation rules

**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:

=AND($B2<>"Off",$B2<>"not available",$B2<>"request off",C2>=0,C2<1)

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:

E2: =MOD(D2-C2,1)
format to h:mm

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)