Greetings Excel Gurus,
Wondering if you could help me with a formula to achieve this automatically. I am trying to generate a employee schedule and want to achieve the below.
If Employee 1 Schedule start is Sun, then lookup the day from row 2 and enter "D" if (Sun, Mon, Tue, Wed) and "X" if (Thu, Fri, Sat)
If Employee 2 Schedule start is Sat, then lookup the day from row 2 and enter "D" if (Sat, Sun, Mon, Tue) and "X" if (Wed, Thu, Fri)
If Employee 3 Schedule start is Mon, then lookup the day from row 2 and enter "D" if (Mon, Tue, Wed, Thu) and "X" if (Fri, Sat, Sun)
Wondering if you could help me with a formula to achieve this automatically. I am trying to generate a employee schedule and want to achieve the below.
01-Jan | 02-Jan | 03-Jan | 04-Jan | 05-Jan | 06-Jan | 07-Jan | 08-Jan | 09-Jan | 10-Jan | 11-Jan | 12-Jan | 13-Jan | 14-Jan | 15-Jan | 16-Jan | 17-Jan | 18-Jan | 19-Jan | 20-Jan | 21-Jan | 22-Jan | 23-Jan | 24-Jan | 25-Jan | 26-Jan | 27-Jan | 28-Jan | 29-Jan | 30-Jan | 31-Jan | |||
Schedule Start | Schedule End | Wed | Thu | Fri | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Mon | Tue | Wed | Thu | Fri | |
Employee 1 | Sun | Wed | D | X | X | X | D | D | D | D | X | X | X | D | D | D | D | X | X | X | D | D | D | D | X | X | X | D | D | D | D | X | X |
Employee 2 | Sat | Tue | X | X | X | D | D | D | D | X | X | X | D | D | D | D | X | X | X | D | D | D | D | X | X | X | D | D | D | D | X | X | X |
Employee 3 | Mon | Thu | D | D | X | X | X | D | D | D | D | X | X | X | D | D | D | D | X | X | X | D | D | D | D | X | X | X | D | D | D | D | X |
Employee 4 | Tue | Fri | X | X | D | D | D | D | X | X | X | D | D | D | D | X | X | X | D | D | D | D | X | X | X | D | D | D | D | X | X | X | D |
Employee 5 | Sun | Wed | D | X | X | X | D | D | D | D | X | X | X | D | D | D | D | X | X | X | D | D | D | D | X | X | X | D | D | D | D | X | X |
If Employee 1 Schedule start is Sun, then lookup the day from row 2 and enter "D" if (Sun, Mon, Tue, Wed) and "X" if (Thu, Fri, Sat)
If Employee 2 Schedule start is Sat, then lookup the day from row 2 and enter "D" if (Sat, Sun, Mon, Tue) and "X" if (Wed, Thu, Fri)
If Employee 3 Schedule start is Mon, then lookup the day from row 2 and enter "D" if (Mon, Tue, Wed, Thu) and "X" if (Fri, Sat, Sun)