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

 

[Peter_SSs]

Like this?

Excel Workbook
	A	B
2	23/03/2011 14:35	PM Shift
3	23/03/2011 19:37	PM Shift
4	24/03/2011 0:39	Night Shift
5	24/03/2011 5:42	Night Shift
6	24/03/2011 10:44	AM Shift
7	24/03/2011 15:47	PM Shift
Shift

 

[shonaart]

Hi
Thanks, thats fantastic, works great
Thank you for your time
Vaughan

 

[Peter_SSs]

I think that you could tidy that a little by just having the 3 shift options in the LOOKUP function.

=LOOKUP(MOD(A2+2/24,1),{0,0.3333,0.6667},{"Night","AM","PM"})&" Shift"

 

[fgbuk]

"Hi I am trying to work timesheet in any given 7 days period of variable rate Day, Night, Saturday and Sunday rate only have two input example start 22/07/2011 16:00 finish 23/07/2011 04:00 which is 12 hours but night rate kicks in at 18:00 which means in this case 2 hours in day rate and 10 hours in night rate.
Saturday/Sunday rate start and finish 0000 to 2359 I HAVE looked at a few IF/AND statements to separate the different hours from each 24 hour shift to work out hourly rates then add them together to get total but I am faced with brick wall : please help"

 

[shonaart]

Thank you Petter SS for the help with the Formula it works great however would it possible for the formula to distinguish between a weekday and weekend.

Monday to friday there are three shifts ( AM, PM, Night )
Weeekends, Sat & Sun there are two shifts ( AM , Night )

Would it be possible to add this into the formula , it would be a great help thanks.
Vaughan

 

[Peter_SSs]

Thank you Petter SS for the help with the Formula it works great however would it possible for the formula to distinguish between a weekday and weekend.

Monday to friday there are three shifts ( AM, PM, Night )
Weeekends, Sat & Sun there are two shifts ( AM , Night )

Would it be possible to add this into the formula , it would be a great help thanks.
Vaughan

It is quite likely possible. However, to suggest a formula would require us to know more about the changed requirements.

For example, are the 'AM' and 'Night' shift times the same on weekends?

- If so, what do you want returned if a weekend time is outside those shift times?

- If not, tell us more about what happens on weekends.

Some varied weekend sample data and expected results might help clarify.

 

[shonaart]

Hi Peter, thanks for the reply

1. Yes the times are the same every saturday and Sunday, 06H00 to 18H00 ( AM ) and them 18H00 to 06H00 PM shift.
It is also like that on public holidays but you do not have to worry about that as that will get too complex then.

So im summary , they work:

1. Monday to Friday AM = 06H00 to 14H00
PM = 14Hoo to 22H00
Night = 22H00 to 06H00

2. Saturday & Sunday AM = 06H00 t0 18H00
Night = 18H00 to 06H00

Thanks for your help, much appreciated

 

[Peter_SSs]

Building that into a single formula would make it quite a long formula. I suggest setting up some lookup tables and helper cells and then the formula is simpler. It should also cope with public holidays.

Column D contains a list of public holiday dates.
Columns F:I contain the lookup tables for the different types of shifts.

Formulas in B2 and C2 are copied down.

(All of columns C:I could be hidden after they are populated.)

I have just formatted columns A and D to show the day so it was easier for checking.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Date/Time	Shift	Normal Day	Public Holidays		Shifts
2	Wed 23/03/11 14:35	PM	TRUE	Mon 28/03/11 0:00		Normal		W/End or Pub Hol
3	Wed 23/03/11 19:37	PM	TRUE	Fri 27/05/11 0:00		Start Time	Shift	Start Time	Shift
4	Thu 24/03/11 0:39	Night	TRUE			0	Night	0	PM
5	Thu 24/03/11 5:42	Night	TRUE			0.25	AM	0.25	AM
6	Thu 24/03/11 10:44	AM	TRUE			0.5833	PM	0.75	PM
7	Thu 24/03/11 15:47	PM	TRUE			0.9167	Night
8	Sat 26/03/11 5:42	PM	FALSE
9	Sat 26/03/11 10:44	AM	FALSE
10	Sat 26/03/11 15:47	AM	FALSE
11	Mon 28/03/11 5:42	PM	FALSE
12	Mon 28/03/11 10:44	AM	FALSE
13	Mon 28/03/11 22:47	PM	FALSE
14	Tue 29/03/11 22:47	Night	TRUE
Shift

 

[brao]

thank u