If statement with Date & time

[shonaart]

Hi
Please could you help

Column A : Date & time ( 23 march 2011 14:35 )

In column B: I want the formula to look at column A and return three possible asnswers.

If the time is between 06:00 to 13:59 = AM shift
If the time is between 14:00 to 21:59 = PM Shift
If the time is between 22:00 to 06:00 = Night Shift.

Remember there is a dat attached to the time. I do not want to split the date & time pls.

Thanks
Vaughan

 

[shonaart]

Hi Peter,
Sorry late response, been on leave. Thank you, its perfect.
Thanks
Vaughan

 

[Peter_SSs]

Sorry late response, been on leave. Thank you, its perfect

Lucky you! I was wondering where you were.
Glad it works for you.

 

[shonaart]

HI Peter_SSS
Thanks for all the help in the past.
Is it possible to do the the above formula without adding other Columns, one that looks at the date and time and returns the answer ?

Thanks

 

[Peter_SSs]

Is it possible to do the the above formula without adding other Columns, one that looks at the date and time and returns the answer ?

Yes, except that you had mentioned the issue of public holidays being treated as a weekend day. My previous suggestion did cover that case. If we don't have a list of public holidays, we cannot allow for them.

The formula in column B below uses no helper columns but gives incorrect results in B11 and B13 given the dates and public holidays shown (same as I used in post #9).

If you at least allow a list of public holidays, then the formula in column C should work for those too.

Excel Workbook
	A	B	C	D
1	Date/Time	Shift	Shift	Public Holidays
2	Wed 23/03/11 14:35	PM	PM	Mon 28/03/11
3	Wed 23/03/11 19:37	PM	PM	Fri 27/05/11
4	Thu 24/03/11 0:39	Night	Night
5	Thu 24/03/11 5:42	Night	Night
6	Thu 24/03/11 10:44	AM	AM
7	Thu 24/03/11 15:47	PM	PM
8	Sat 26/03/11 5:42	PM	PM
9	Sat 26/03/11 10:44	AM	AM
10	Sat 26/03/11 15:47	AM	AM
11	Mon 28/03/11 5:42	Night	PM
12	Mon 28/03/11 10:44	AM	AM
13	Mon 28/03/11 22:47	Night	PM
14	Tue 29/03/11 22:47	Night	Night
15
Shift

 

[shonaart]

HI Peter, thanks so much.
I am going to ignore the public holidays. But the weekends are important. I do the publice holidays manually if I need to.
Thanks so much for your time.