This post was updated 01/08/2016
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.
Removing an Array Formula
There are several calendar templates available for download within Excel. However many of these are built around an array formula. If this is the case for your calendar, you will not be able to change the cell contents. To remedy this, you will need to copy the cell contents and paste just the values, not the underlying array formula, before you can track days.
- Select the entire calendar.
- Right-click and select Copy.
- With the “marching ants” still circling your selected calendar, and without moving your mouse, right-click and select Paste Values
- It is important that the values be pasted exactly on top of the calendar.
Now that the calendar is set up correctly, we are ready to begin.
Our calendar includes an area to track our vacation days (AM8:AT10). We will work with this a little later.
Applying Conditional Formatting
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.
We will begin on the Home tab, in the Styles group:
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:
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.
Using the Countif Function
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 (AM8:AT10) 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 B9:AI32. 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 AT8) and type: =countif(B9:AI32,”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(B9:AI32,”v”), type: +.5*(countif(B9:AI32,”h”)).
The completed function should read: =countif(B9:AI32,”v”)+.5*(countif(B9:AI32,”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, AT10.) Type: =AT8-AT9. Press Enter.
The tracker now shows us how many days of vacation we have left to use.