In this article, we are going to show you how to create a dynamically changing calendar in Excel.

## Populate variables

First step is creating the variables to be used in the formulas. These variables need to provide the present day information so that the calendar calendar can update automatically. Here is the list of variables:

- Present date
- The number of the present day of the month
- The number of the present month
- Present year
- The date of the first day of the present month
- The number of the present day of the week
- The number of the present week of the year

We will be using the **TODAY****, ****DAY****, ****MONTH****, ****YEAR****, ****DATE****, ****WEEKDAY,** and **WEEKNUM** functions. Briefly, while **TODAY **function returns the date of the present day, **DAY, MONTH, YEAR, WEEKDAY** and **WEEKNUM** functions parse the respective date values. The **DATE** function returns the date value of the given year, month and day.

In the following screenshot, you can see the names we have given and formulas which were generated on November 20^{th}, 2020.

We named each variable to make our job easier when creating formulas. Next step to create a calendar in Excel is creating an outline for the calendar interface.

## Outline for the Calendar in Excel

Before creating the actual formulas that generate the days of the month, we first need to place an outline to help creating formulas and also provide visual reference.

The days will be placed on a table of 7 columns and 6 rows. We need to increase these numbers by 2 for titles and helper cells as well. While the column and row titles include week days for columns and week numbers by rows, the helper cells will be consecutive numbers starting from 1.

The calendar might look like below.

Use borders or background colors as you'd like. The important point is using a dynamic week number in the title. Reference the **WEEKNUM** function for the first week, and add 1 for each consecutive week.

F | |

4 | =WeekNumber |

5 | =F4+1 |

6 | =F5+1 |

7 | =F6+1 |

8 | =F7+1 |

9 | =F8+1 |

Let's continue creating a calendar in Excel by populating the day numbers.

## Generating Days

If you are using a similar layout, and don't want to worry about the details of the formula, feel free to copy the formula below. Then paste it into the top-left cell on your calendar’s day area, and populate for the remaining cells. The **G2** and **E4** cells refer the first cells of the helper columns. Thus, you may need to adjust these references, unless the first cell is **G4**.

Now, let's take a closer look at the formula. First, the formula uses the **DATE **function, which returns a date by the given year, month and day. The first 2 arguments are supplied by the variables *YearNumber* and *MonthNumber* which have been calculated already.

The day part of the formula aims to generate day numbers on a 7-base, since there's 7 days in a week. *7*($E4-1)* part of the formula check the week number in the helper column E, and generates a base, starting with *0* and increasing by *7* each time.

Week | Base |

1 | 0 |

2 | 7 |

3 | 14 |

4 | 21 |

5 | 28 |

6 | 35 |

We can increase or decrease this number as much as the difference between the week day of the first day of the month by the day’s week number. In other words, we are getting the first day of the present month as an anchor point. For example, it is the 7^{th} day of the week (in *Monday to Sunday* base) for *November 1, 2020*.

If our calendar’s week assumes that *Monday* is the first day of the week, *Monday* should be 6 day before (=1-7) the first day of the month. Thus, we get numbers like below.

Mon | Tue | Wed | Thu | Fri | Sat | Sun |

-6 | -5 | -4 | -3 | -2 | -1 | 0 |

Add these values to 1 to shift the numbers. So, *Sunday* can become the first day like in the following example.

Mon | Tue | Wed | Thu | Fri | Sat | Sun |

-5 | -4 | -3 | -2 | -1 | 0 | 1 |

As a result, we have 2 arrays for 2 dimensions. To create a table, we need to add both arrays together.

These numbers represent the day of the present month. There are numbers less than 1 or greater than 31, which obviously can’t be days of the month.

The **DATE** function handles these types of numbers by generating a date in the previous month or the next. For example, -2 becomes two days before the given month’s first day.

Finally, if you use these numbers in the **DATE **function, you can get the actual dates of the current month.

## Formatting

### Day numbers

Now, let’s populate the days. You can alter the formatting of the values without changing the actual value. This feature is called *Number Formatting*. Follow the steps to adjust the number formatting to show only the days.

- Select any the dates in the day area
- Press
**Ctrl + 1**to open the**Format Cells**dialog - Activate the
**Number**tab if it is not open already - Select
*Custom*in**Category**list - Type in the letter
*d*into the**Type**box - Click
**OK**to save

Now, the calendar layout is done.

If you want to remove the padding for single digit dates, you can use *dd* instead of single *d*. To learn more about number formatting: Number Formatting in Excel - All You Need to Know

### Highlighting the present day

Finally, we will be using **Conditional Formatting to **to set different colors for days that are not in the present month, and highlight the present day.

To add these conditional formatting options;

- Select the day range
- Click the
**Conditional Formatting**icon under the**Home**tab of the Ribbon - Select
**New Rule** - Select
**Use a formula to determine which cells to format** - Enter a formula to define the rule for highlighting the current day
*=G4=TODAY()*G4: Top-left cell of the range includes days. Use a relative reference (do not use $). - Click the
**Format**button in the*Preview*window of the**Format Cells**dialog - In the dialog, select formatting options you want to see in the present day’s cell

We used an orange background in this example. - Click
**OK**to apply - The
**Preview**box will display your preferences - Click
**OK**to apply conditional formatting

*=G4=TODAY()*

The formula returns a *Boolean *value. If the cell value (G4) is equal to result of **TODAY** function, which returns the present day in a date format, the formula returns *TRUE*. Otherwise, *FALSE*. If the result is *TRUE*, Excel applies the formatting into the cell.

There are 2 important points here:

- You need to use a relative reference, e.g.
*G4*, unless you do not want Excel to populate the reference along the range. Consider how formulas change when you copy them elsewhere. Excel applies the same for conditional formatting formulas as well. For more information, see: How to create an Excel absolute reference and relative reference - The day cells must return the actual date values. This is why we are using
*number formatting*to show the day portions.

### Eliminating the days not in the present month

Here is the formula to give a different formatting for days that are not in the present month. They will be grayed out in our example:

Final step is hiding the helper columns.

This is how you can create a dynamically changing calendar in Excel.