Shift Schedule Formula
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. |
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 |
All times are GMT +1. The time now is 04:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com