work out shift from day and time

[tintin1012000]

HI all

I need to update a formula to tell me shift Day / Evening or Night

in Column V i have the hour, 1 to 24
In column W i have minute 00 to 60
in Column Z i have the Day Monday to Sunday

Day shift is from 08.00 to 16.30 Monday to Thursday and 08.00 to 15.30 on Friday

Evening shift is 16.31 to 12.30 Monday to Thursday and 15.31 to 22.30 on Friday

Night shift is Monday to Thursday 12.31 to 07.59 and 22.31 am to 07.30 on Friday

any genius know what to do ?

cheers

 

[Scott Huish]

I think you can do this with a simple LOOKUP because only certain shifts starts on certain times regardless of the day.

=LOOKUP(V2,{8,12,15,22},{"Day","Night","Evening","Night"})