Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for shift schedule & calculating future dates - 2003 | Excel Discussion (Misc queries) | |||
how do i create a work shift schedule | Excel Discussion (Misc queries) | |||
Shift Schedule Spreadsheet | Excel Discussion (Misc queries) | |||
Employees shift schedule | Excel Discussion (Misc queries) | |||
Change formula for Employee Shift Schedule template | Excel Worksheet Functions |