View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
E.Q. E.Q. is offline
external usenet poster
 
Posts: 24
Default Shift Schedule Formula

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