There are several reasons we might need to keep track of particular days over the course of a year: vacation days, sick time, out of town meetings, days specific goals are met, etc. In this example, we will be tracking vacation days, but keep in mind that this could be modified for other purposes.
We will begin with a basic calendar in Excel. There are several calendar templates which can be downloaded.
Our calendar includes an area to track our vacation days (AG3:AH5). We will work with this a little later.
The first thing we want to do is create conditional formatting that will quickly give us a visual representation of our vacation days. When we apply a vacation day (or half day), we want the calendar to color-code those days.
In the Styles group on the Ribbon, click . Point to “Highlight Cells Rules.” Click “Equal to…” A dialog box opens. Here we can create a rule which will apply formatting to a cell if certain conditions are met. In this case, we want the cell itself to be light red and the text to be dark red whenever we mark a day as a vacation day. We will use a simple “v” to indicate a vacation day.
The dialog box should look like this:
. Click OK.
We will follow the same steps to set conditional formatting for half-days. Click “Conditional Formatting.” Point to “Highlight Cells Rules.” Click “Equal to…” We will use “h” to indicate a half-day of vacation time. We want these half-days to show in a different color. Use the drop-down arrow to choose “Green Fill with Dark Green Text.” Click OK.
We can now add vacation days and half-days to our calendar. Click on a date. Type “v” and press Enter. The “v” replaces the number and the day is now red. Choose several more dates as vacation days.
To add a half-day, click on a date and type, “h.” The date is now green. Choose several more dates as half-days.
With the conditional formatting applied, we can easily see which dates were used for vacation or for half-days.
With the dates color-coded, we have an idea of how many vacation days have been used. But, we don’t want to count each of these red and green dates to determine the exact number. We can use the Countif function to have Excel do this for us.
To the right of the calendar there is a range of cells designed to help us keep track of our total number of days, the number which have been used, and the number remaining. For this example, we started the year with 18 days of vacation. Although we’ve inserted vacation days and half-days in the calendar, there is no formula in place to calculate the number of days taken or remaining.
We will use the Countif function to count the number of vacation days (“v”) and half-days (“h”). We will actually have to use more than one Countif funtion. One for “v” and one for “h.”
The syntax for the Countif function is: =countif(range,criteria). We will use the entire calendar as the range. In this example, the calendar range is A5:AE28. The criteria is text (either “v” or “h”), but criteria can be a value, cell reference, or expression. Text references should be placed in quotes.
We will first enter the Countif function to determine the number of vacation days. Click in the cell next to “Days Used” (in this example AH4) and type: =countif(A5:AE28,”v”).
The Countif function to count the number of half-days is similar. We still want Excel to count the number of times we inserted “h” into our calendar, but to count them as half-days. To do this, we will multiply the result of the Countif function by .5.
Immediately following =countif(A5:AE28,”v”), type: +.5*(countif(A5:AE28,”h”)).
The completed function should read: =countif(A5:AE28,”v”)+.5*(countif(A5:AE28,”h”)).
We can now see how many vacation days we’ve used (both full and half-days).
To find the number of days remaining, we just need to enter a simple formula. Click in the cell next to “Days remaining.” (In this example, AH5.) Type: =AH3-AH4. Press Enter.
The tracker now shows us how many days of vacation we have left to use.