I'm trying to update and streamline a spreadsheet that was created before I took over, that tracks vacation requests for the year for the 20+ employees I oversee.
Using Microsoft Office Home and Business 2016 at work.
Sample of existing spreadsheet:
Explanation of spreadsheet:
Thanks in advance for all your help.
Using Microsoft Office Home and Business 2016 at work.
Sample of existing spreadsheet:
| A | B | C | D | E | F | G | H | I | |
| 1 | Vacation | Schedule | 2024 | ||||||
| 2 | Staff Member | Vacation Request Start Date | Vacation Request End Date | Return to Work Date | # of Weeks Requested | Paid on Week Ending | Total # of Weeks Allowed for the year | Total # of Weeks Taken So Far | Total # of Weeks Remaining for the Year |
| 3 | John D. | 4/8/24 | 4/14/24 | 4/15/24 | 1 | 4 | 1 | 3 | |
| 4 | Jane D. | 2 | 2 | 2 | 0 | ||||
| 5 | Jenny L. | 4/15/24 | 4/28/24 | 4/29/24 | 2 | 3 | 2 | 1 | |
| 6 | Jack R. | 5/6/24 | 5/12/24 | 5/13/24 | 1 | 1 | 1 | 0 | |
| 7 | Jane D. | 5/13/24 | 5/19/24 | 5/20/24 | 1 | 2 | 2 | 0 | |
| 8 | John D. | 6/3/24 | 6/16/24 | 6/17/24 | 2 | 4 | 3 | 1 | |
| 9 | Jane D. | 7/8/24 | 7/14/24 | 7/15/24 | 1 | 2 | 2 | 0 |
Explanation of spreadsheet:
- Vacation requests cannot overlap. Only 1 employee on vacation at a time.
- B4:D4 are blank and have no dates but E4 says 2 weeks requested - Employee requested a vacation pay advance and will continue to work. When employee actually takes vacation (Rows 7 & 9), the employee will not be paid those particular weeks.
- Set print area to Columns A through I
- Added a list of staff members' names in Column K
- Converted Column A to a drop down list to choose a name from Column K
- Conditional formatting to Columns C and D - If cells/dates = 0 then white font to make them appear blank
- Columns C and D formulas in relation to Columns B and E. So once I input the date in Column B and how many weeks in Column E, I'd like Columns C and D to automatically calculate the rest of the dates. I tried "=IF(B3=0,0,B3+(6*E3))" in C3 and "=C3+1" in D3. But doesn't appear to be consistent if more than 1 week is taken.
- Column G formula/method to show total weeks of vacation the employee in Column A has for the whole year.
- Column H formula to keep a running count of vacation weeks taken by Column A employee. For example, refer to Rows 3 & 8. But also take into consideration the above 2nd bullet point in Explanation.
Thanks in advance for all your help.
