Note: The instructions below do not teach you how to format the sheet. I assume that you know how to change the font size, font style, insert rows and columns, add borders, change the background color, etc.
How to create a calendar in Excel
The January 2025 calendar will look like this.
So how do you know that January 2025 starts with a Wednesday?
first. First, to make the formulas easier to understand, I created named regions as follows.
| Name | Cells |
|---|---|
| CalendarYear | K5 |
| JanOffset | =DATE(CalendarYear,1,1)-WEEKDAY(DATE(CalendarYear,1,1)) |
Explanation: dates are stored as numbers in Excel and count the number of days since January 0, 1900. The DATE function returns the number representing the date. The DATE function accepts three arguments: year, month, and day. The WEEKDAY function returns a number from 1 (Sunday) to 7 (Saturday) representing the day of the week. For example, January 1, 2025 is 45658. This date falls on a Wednesday. In other words, =DATE(CalendarYear, 1,1) equals 45658 and WEEKDAY(DATE(CalendarYear, 1,1)) equals 4. The result is JanOffset equal to 45658 – 4 = 45654.
2. Take a look at the formula in cell B4.
3. Take a look at the formula in cell E4.
4. We will change the format to “date format” to display only the day of the month.
Note: in the same way, you can create formulas for other dates in January and other months.
In addition, you can also view many other excel articles here.
