We still want Excel to count the number of times we inserted “h” into our calendar, but to count them as half-days. The Countif function to count the number of half-days is similar. Click in the cell next to “Days Used” (in this example AT8) and type: =countif(B9:AI32,”v”). We will first enter the Countif function to determine the number of vacation days. 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 use the entire calendar as the range. The syntax for the Countif function is: =countif(range,criteria).
We will actually have to use more than one Countif funtion. We will use the Countif function to count the number of vacation days (“v”) and half-days (“h”). 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. For this example, we started the year with 18 days of vacation. 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. We can use the Countif function to have Excel do this for us. But, we don’t want to count each of these red and green dates to determine the exact number. With the dates color-coded, we have an idea of how many vacation days have been used. With the conditional formatting applied, we can easily see which dates were used for vacation or for half-days. To add a half-day, click on a date and type, “h.” The date is now green. The “v” replaces the number and the day is now red. Choose several more dates as vacation days. We can now add vacation days and half-days to our calendar. Use the drop-down arrow to choose “Green Fill with Dark Green Text.” Click OK. We want these half-days to show in a different color. (Click “Conditional Formatting.” Point to “Highlight Cells Rules.” Click “Equal to…”) We will use “h” to indicate a half-day of vacation time. We will follow the same steps to set conditional formatting for half-days.
We will use a simple “v” to indicate a vacation day. 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. Here we can create a rule which will apply formatting to a cell if certain conditions are met. We will begin on the Home tab, in the Styles group:Ī dialog box opens. When we apply a vacation day (or half day), we want the calendar to color-code those days. The first thing we want to do is create conditional formatting that will quickly give us a visual representation of our vacation days. Our calendar includes an area to track our vacation days (AM8:AT10).
Now that the calendar is set up correctly, we are ready to begin.
#Microsoft excel 2011 calendar download
There are several calendar templates available for download within Excel. In this example, we will be tracking vacation days, but keep in mind that this could be modified for other purposes. 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.
0 kommentar(er)
