Check a users input based on a Validation selection, Start and End time

[-emma-]

Hi all,

I am needing to capture working times from new staff members. This is something I have done for quite some time, but I am looking at making this easier for me and put some checking in there so the users can see where they have an error.

In the attached image is a snippet of what the sheet looks like.

A2 and below is a Validation List of 4 possible options, shown in A6:A9 for ease. What I require is a check that looks at what the user has put in A2 and then either B2 or C2 depending.
If A2 = 08:00 B2 needs to be equal or earlier than A2
If A2 = 09:00 B2 needs to be equal or earlier than A2
If A2 = 17:00 C2 needs to be equal or later than than A2
If A2 = 18:30 C2 needs to be equal or later than than A2

The result will show in E2 with either a tick or a cross showing if completion was correct or not correct.

Hope this is makes sense and I hope someone with much more knowledge can help me out with this

Thank you for your time.

Emma

 

[steve the fish]

Maybe something like this, it doesnt bother checking times 3 and 4 as you use validation. If it isnt 1 and 2 then it must be 3 or 4.

=IF(OR(A2=8/24,A2=9/24),IF(B2<=A2,"OK","No Good"),IF(C2>=A2,"OK","No Good"))

 

[-emma-]

Maybe something like this, it doesnt bother checking times 3 and 4 as you use validation. If it isnt 1 and 2 then it must be 3 or 4.

=IF(OR(A2=8/24,A2=9/24),IF(B2<=A2,"OK","No Good"),IF(C2>=A2,"OK","No Good"))

This works perfectly, thank you