The Time-Off Planner (TimeOffPlanner.hta) is a handy HTML Application (HTA) that you can use to help keep track of how much vacation time you have available throughout the year. As you enter the vacation time you're taking off, it automatically deducts those hours from your vacation-hours balance. If you're an on-call staff member, the Time-Off Planner color-codes the weeks you're on-call so that you can avoid scheduling time off during those weeks.
The Time-Off Planner creates a Microsoft Excel spreadsheet that shows in weekly increments how much vacation time is available. For me, this view makes it easy to evenly spread out my vacation time throughout the year.
Figure 1 shows the Time-Off Planner's UI when it first opens. To use this HTA, you just need to plug in a few values:
- Enter how many vacation hours you currently have available in the first input box. Pay stubs often include this number. Alternatively, you can get it from your HR representative.
- Enter the number of vacation days you earn per year in the second input box. For example, if you get two weeks of vacation, you'd enter 10.
- Enter the date when you'll be on-call next in the third input box. If you’re lucky enough not to be on-call, simply leave this input box empty. If you enter an on-call date, it's automatically adjusted to the Monday of the week that you enter. (This date needs to be a Monday to correlate with the weeks of the year listed in the first column in the spreadsheet.)
- Enter how often your on-call duty rotates in terms of number of weeks in the last input box. For example, if you're on-call once a month, you'd enter 4. If you’re not on-call, leave this input box empty.
Figure 2 shows an example of how you might fill in the input boxes. You can press the F1 key while your cursor is in any of the input boxes for context-sensitive help. For the general help screen, you can click anywhere on the HTA's background and press F1.
After you fill in the appropriate text boxes, you just need to click Create Worksheet. Figure 3 shows an example of what the spreadsheet might look like before you make any vacation entries. Notice that the AvailableVacHrs column automatically calculates the number of vacation hours you'll have available each week. Also notice that the on-call weeks are in blue text in the Week column so you can easily plan your vacation time around those weeks.
After you create and save the spreadsheet, all you need to do is enter the number of vacation hours you plan to take in the RequestedVacHrs column and when you plan to take those hours in the StartingVacHrs column. When you enter a value in the RequestedVacHrs column, the vacation balance is automatically adjusted for the remaining weeks. Figure 4 shows what the spreadsheet might look like after you’ve made some vacation entries.
The Time-Off Planner assumes that your workday is 8 hours long when calculating the number of vacation hours accrued each week. (The length of a workday varies among organizations. If you're uncertain, check with your HR representative.) If your workday is shorter or longer, you can adjust the formula used in this calculation. Simply click the first entry under VacHrsPerWk (cell F2) and change the formula accordingly. For example, if your workday is 7.5 hours, you'd change the formula from (E2*8)/52 to (E2*7.5)/52. As you can see, this worksheet is easy to modify to fit your situation.
You can download the Time-Off Planner by clicking the Download the Code Here button near the top of the Web page. The Time-Off Planner has a lot of interesting VBScript code that you might want to explore, particularly if you're interested in creating .vbs scripts that use Excel for output or in providing context-sensitive help screens within your HTAs. To view the HTA's code, right-click somewhere in the HTA background and select View Source. Alternatively, you can open the HTA in Notepad.
I hope the Time-Off Planner helps you track and spread out your hard-earned days off!
End of Article
