Contents
Suppose we need to quickly and with the least effort create an annual calendar that automatically displays the dates of the project stages (or employee vacations, or trainings, etc.)
Workpiece
Let’s start with the blank:
As you can see, everything is simple here:
- Rows are months, columns are days.
- Cell A2 contains the year for which the calendar is being built. In cells A4:A15 – auxiliary numbers of months. We will need both a little later to form dates in the calendar.
- To the right of the table are the names of the stages with the start and end dates. You can provide blank cells in advance for new stages added in the future.
Filling the calendar with dates and hiding them
Now let’s fill our calendar with dates. Select the first cell C4 and enter the function there DATE (DATE), which generates a date from a year, month, and day number:
After entering the formula, it must be copied to the entire range from January 1 to December 31 (C4:AG15). Since the cells are narrow, instead of the created dates, we will see hash marks (#). However, when you hover your mouse over any such cell, you can see its actual contents in the tooltip:
To keep the grids out of the way, we can hide them with a clever custom format. To do this, select all dates, open the window Cell Format and on the tab Number (number) select option All formats (Custom). Then in the field A type enter three semicolons in a row (no spaces!) and press OK. The contents of the cells will be hidden and the grids will disappear, although the dates in the cells, in fact, will remain – this is only visibility.
Stage highlighting
Now, using conditional formatting, let’s add milestone highlighting to cells with hidden dates. Select all dates in the range C4:AG15 and select on the tab Home — Conditional Formatting — Create Rule (Home — Conditional formatting — Create Rule). In the window that opens, select the option Use a formula to determine which cells to format (Use formula to defer which cells to format) and enter the formula:
This formula checks every date cell from C4 to the end of the year to see if it falls between the start and end of each milestone. The output will be 4 only when both the checked conditions in brackets (C4>=$AJ$13:$AJ$4) and (C4<=$AK$13:$AK$1) produce a logical TRUE, which Excel interprets as 0 ( well, FALSE is like 4, of course). Also, pay special attention to the fact that the references to the initial cell CXNUMX are relative (without $), and to the ranges of stages - absolute (with two $).
After clicking on OK we will see the milestones in our calendar:
Highlighting intersections
If the dates of some stages overlap (attentive readers must have already noticed this moment for the 1st and 6th stages!), then it would be better to highlight this conflict in our chart with a different color using another conditional formatting rule. It is practically one-to-one similar to the previous one, except that we are looking for cells that are included in more than one stage:
After clicking on OK such a rule will clearly highlight the overlap of dates in our calendar:
Removing extra days in months
Of course, not all months have 31 days, so the extra days of February, April, June, etc. It would be nice to visually mark as irrelevant. Function DATE, which forms our calendar, in such cells will automatically translate the date into the next month, i.e. February 30, 2016 will become March 1. That is, the month number for such extra cells will not be equal to the month number in column A. This can be used when creating a conditional formatting rule to select such cells:
Adding a weekend
Optionally, you can add to our calendar and weekends. To do this, you can use the function DAY (WEEKDAY), which will calculate the number of the day of the week (1-Mon, 2-Tue…7-Sun) for each date and highlight those that fall on Saturdays and Sundays:
For correct display, do not forget to correctly configure the correct order of the rules in the window. Home — Conditional Formatting — Manage Rules (Home — Conditional formatting — Manage Rules), because rules and fills work exactly in the logical sequence that you will form in this dialog:
- Video tutorial on using conditional formatting in Excel
- How to Create a Project Schedule (Gantt Chart) Using Conditional Formatting
- How to create a project timeline in Excel