J
John Calder
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 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
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