"John Calder" wrote:
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)

Try:

=LOOKUP(TEXT(MOD(A1,1),"hh:mm:ss"),
{"00:00:01","07:20:00","15:30:00","23:20:00"},
{"NIGHT","DAY","AFTERNOON","NIGHT"})


I quoted the wrong time format (24hr clock) I have edited the original
question and updated it to the correct format ( 12hr clock)

The format of the cell (A1) does not matter. The internal form (date/time
serial number) remains the same. The TEXT() expression above converts the
serial number to 24-hour time, which is needed for the proper lookup
comparison.


----- original message -----

"John Calder" wrote in message
...
Hi

I quoted the wrong time format (24hr clock) I have edited the original
question and updated it to the correct format ( 12hr clock)

Cheers

"John Calder" wrote:

Hi

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 3:29pm
Afternoon shift starts at 3:30pm and ends at 11:19pm
Night shift starts at 11: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 wa based on a 24 hr clock time format.

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