J

#### John Calder

I run Excel 2K

I download data from a mainframe. This data has a date & time format in it

(dd/mm/yyyy hh:mm:ss)

I work in a place that has a 3 shift cycle - day shift, afternoon shift,

night shift.

Day shift starts at 7:20am and ends at 15:29pm

Afternoon shift starts at 15:30pm and ends at 23:19pm

Night shift starts at 23:20pm amd ends at 7:19am

I need a formula that looks at the cell with the date/time in it and

displays the word DAY (for the time frame of day shift), AFTERNOON (for the

timeframe of afternoon shift) and NIGHT (for the timeframe of night shift)

In my previous job I used a formula for a different shift pattern which was

12 hour one which worked really well however the shift pattern that I need is

for is an 8 hour one, and I dont know how to edit the formula I used for the

12 hour pattern.

This is the formula I used for the 12 hour shift pattern:

=IF(AND(TEXT(F12-INT(F12),"hh:mm:ss")>="07:00:00",TEXT(F12-INT(F12),"hh:mm:ss")<"19:00:00"),"Day","Night")

I hope I have managed to explain this ok.

Thanks

John