Shift Schedule Formula

[Dan]

Hello. Need a formula that will return the shift name (A/E/C/F) in cell A2
based on the current system date and time. I am not sure how to make it
happen.

My shift schedule is below:
A-Shift: 07:00 to 19:00 Sunday, Monday,Tuesday and every other Wednesday
C-Shift: 07:00 to 19:00 Every other Wednesday, Thursday, Friday and Saturday
E-Shift: 19:00 to 07:00 Every other Saturday, Sunday, Monday and Tuesday
F-Shift: 19:00 to 07:00 Wednesday, Thursday, Friday and every other Saturday

Is this possible? Thanks for your help.

 

[E.Q.]

I created the following formula and put it in cell A2... it seemed to work.

=IF(AND(NOW()-TODAY()>TIME(7,0,0),NOW()-TODAY()<TIME(19,0,0)),IF(WEEKDAY(TODAY())<4,"A",IF(WEEKDAY(TODAY())>4,"C",IF(ISEVEN(TODAY()),"A","C"))),IF(WEEKDAY(TODAY())<4,"E",IF(WEEKDAY(TODAY())<7,"F",IF(ISEVEN(TODAY()),"E","F"))))

One thing to note, the biweekly saturday and wednesday determination is
based on the parity of the date stamp. If this formula will work for you,
then you'll want to check which shift a particular date falls by comparing
the parity of a selected wednesday. for example, the date stamp for wed jan
6 is even and my formula placed it in shift "A" while wed jan 13 is odd and
my formula assigns that to shift "C". If these are backwards, you'll need to
edit the formula. A similar condition exists for the "E" and "F" saturdays.

Hope this helps

Peace