How to create an automatic calendar with Excel where the colors of weekends and holidays change automatically for each month. This article will detail you step-by-step how to build it.
For a calendar that saves data across months, consider exploring the resources in this article.
Step 1: Add employee names
In column A, write the names of your employees.
Step 2: Add a drop-down menu as an object.
It is very easy to create drop-down menus in Excel, but with this technique, it is not possible to attach a macro to them.
In this workbook, the macro will be used to hide the days according to the number of days in the month. So we must add a drop-down menu as an object.
- Go to the menu File>Options
- Then Customize the Ribbon
- Check the Developer option in the right pane
Step 3: Insert an object Drop-down menu
- Set yourself to A1 to create the drop-down menu for the months.
- Now on the ribbon, select Developer > Insert > combo box
With the mouse, click and stretch to make your "Drop-down Menu" object appear in your spreadsheet
Step 4: Create the monthly list
Now we will create the list of months somewhere in our workbook (in a fairly distant column).
Don't waste time writing months one after the other, the copy handle does it for you.
Step 5: Link the drop-down menu to a cell
Next, you must link the Drop-down menu object to a cell in the workbook to retrieve the selected value.
- Select your drop-down object
- Right-click
- Select Control Format
The following dialog box opens
- Select the Control tab (the last one)
- Select the range of month (in this example AH1:AH12
- In the Cell link, select A1 (explanation, just under)
What is the cell link?
- If you select May, the 5th element in the list, the value in A1 will be 5
- And if you select September the value in A1 will be 9
- ...
Step 6: Drop-down menu for years
Reproduce the same manipulations to have a drop-down menu for the years
- Create a column for years
- Insert a new drop-down menu
- Link the year's column with the new drop-down menu
- Associate the drop-down menu with cell A2
Step 7: Create the date according to the selected month and year
We will now create a formula that will retrieve the cells linked A1 (for months) and A2 (for years) to return on the first day of the month.
To do this, we will use the DATE function with the contents of the linked cells.
- For A1, the cell of the month, it's very simple; Just take the data as it is in the formula
- For A2, the drop-down menu will return the values 1, 2, 3, ... corresponding to the selected value. To make this value coincide with a year, a fixed value must be added. For example, add 2014 to the linked cell to create the year 2015.
The formula is therefore
=DATE(A2+2016,A1,1)
Step 8: Create the other days of the month
To calculate the other days, the formula is easier. Just add 1 to the previous cell and copy this formula on the data range C6 to AF6
=B6+1
Step 9: Change the date format
In this step, we will change the format of the dates to make the day appear in letters and numbers
- Select all your dates from line 6 (from B6 to AF6)
- Open the Number Format dialog box (keyboard shortcut Ctrl + 1 or Home>Number Format>Other Numeric Formats ...
- Select the Custom category and enter the ddd dd format in the Type box
Step 10: Change the orientation of the text
Now we will change the orientation of the dates to display them vertically.
- Select B6:AF6
- Activate the menu Home
- Orientation
- Rotate Text Up
And to finish the job, adjust the size of the columns from B to AF
- Select columns B:AF
- Right-click in the column header
- Choose Column Width
- Set the column width to 2.5
The timetable is progressing very well. By changing the values in the drop-down menus, you see the days of the selected month.
Step 11: Format your calendar
Add borders and color to your calendar.
Step 12: Add a dynamic title
We will now create a title that will adapt to the selected month and year.
First and last date of the month
The formula for the first day is:
=DATE(A2+2016,A1,1)
The formula for the last day of the month is:
=DATE(A2+2016,A1+1,1)-1
Formula for dynamic title
All we have to do is insert these 2 functions into a TEXT function
="Period from the "&TEXT(DATE(A2+2016,A1,1),"dd mmmm yyyy") &" to the "&TEXT(DATE(A2+2016,A1+1,1)-1,"dd mmmm yyyy")
And the result becomes:
Step 13: Change the color of weekends and holidays
To change the colors of the holidays, we will use conditional formatting and especially methods with custom formulas. We will create 2 rules
- one for weekends
- another for public holidays.
Rule for weekends
- Select your B6:AF13 data range
- Create a new conditonal formatting rule (Home>Conditional Formatting>New Rule)
- Select the Use a formula option to determine which cells the format will be applied to
- Write the formula =EMDAYS(B$6,2)>5
- Change the fill color (here from orange)
Very, very, VERY IMPORTANT ❗❗❗ There is only one $ in the formula after the column B
Rule for holidays
Here we need to integrate into our workbook the list of public holidays in your country. By clicking on this link, you will find the formulas to calculate the US public holidays.
The formula for this rule is different from the previous calculation. Here we will use the COUNTIF function
- Select all your data (cells B6:AF13)
- Create a conditional formatting rule (Home>Conditionnal Formating>New rules)
- Select Use a formula to determine which cells to format
- Write the formula =COUNTIF(Holidays!$B$2:$B$4,B$6)>0
- Change the background color (red)
Check your rules
Open the conditional rules manager (Home>Conditional Formatting>Manage Rules), you can view the 2 rules created in your spreadsheet.
Don't forget to select This spreadsheet from the first drop-down menu to view all rules.
Step 14: Hide the last columns by Macro
Since not all months have the same number of days, we will hide the columns not belonging to the selected month.
All the explanations about the code are given in this article
The program will read the AD, AE and AF columns to verify that the value of the month in these cells is equal to the value contained in A1 (value of the selected month). If not, the column is hidden.
- Press Alt + F11 to open the Visual Basic Editor
- From the Insert menu, choose Module
- Copy Paste the following code in your module
Sub Hide_Day()
Dim Num_Col As Long
'This instruction clears the content of the cells in your calendar
Range("B7:AF13").ClearContents
For Num_Col = 30 To 32
' Test if the month of the cells (row 6) is the same as the month selected (cell A1 or cells(1,1))
If Month(Cells(6, Num_Col)) >= Cells(1, 1) Then
Columns(Num_Col).Hidden = True
Else
Columns(Num_Col).Hidden = False
End If
Next
Step 15: Link the drop-down menus to the macro
To finish the job, we still have to link the 2 drop-down menus with the macro. This way, with each new selection, the macro will be launched.
- Select the Combo Box
- Right-click on it.
- Choose Assign Macro
- Select the name of the macro (Hide_Day)
- Click OK
- Do the same operation again for the second drop-down menu
Now, whenever you select a month or year from the drop-down menus, your calendar will update and the columns will hide (or show) automatically.
17/02/2023 @ 17:20
Hi the only problem I have is right at the end, for e.g. if I fill the cell for one of the days in February, then change the month to March I can't see no content which I want. However after I click on March and want to go back to February all my content disappears, which I don't want. So basically if I populate info for any month and the click off and come back to that month, everything that I populated it with disappears. Is there anyway to over come this.
17/02/2023 @ 15:13
My days don’t change? What did I do wrong?
24/11/2022 @ 18:38
so i have reached the point to highlight the public holidays.
now i want a different column to count how many workdays each employee has. i used the code =COUNTIF(D10:AH10; "O") and i mark with an "O" each work day of each person. but what if i want to count working weekends and working holidays separately? so that when i fill in the calendar i can see how many sundays and saturdays each employee has to work?
17/11/2022 @ 19:48
Hi Frédéric and thanks for this very helpful tutorial!!
I just have one tiny issue which I hope you can help out with >> When I want to add little "x"s to the month of August for a specific employee, for instance, I see that the "x"s also appear in all the other months as well. How can we restrict that the "x'"s only show in the month of August?
Thank you so much in advance!!
20/11/2022 @ 10:31
I don't understand your question because the calendar used a single worksheet. So how can you have "x" for other months?
17/01/2023 @ 10:42
Τhe "x" are probably the text that an employee works on 1st of august for example. How can we make that information follow the spesific date and only show up on the 1st of august 1st employee cell ?
now whatever you type on one month stays there for all months . we need to save the month and go to the next one starting blank, is that possible?
07/08/2022 @ 06:11
Thank you for this. But how can i change the Weekend. here is Saudi, our weekend is Friday and Saturday.
18/08/2022 @ 14:45
I see, Change the WEEKDAY argument to 5 or 6 (I don't remember exactly) and it will work
07/07/2022 @ 11:10
Thanks for the tutorial. I managed to finish all except the last 2 steps... could not get the proper understanding on how. to....
12/05/2022 @ 21:51
I will say that the macro does not work if you aren't sure what you're building. Especially since you can't see the full workbook they are using. I've just skipped the macro entirely and just made the text white.
10/02/2022 @ 18:05
Thank you for the tutorial. I was able to create the calendar, but would like each cell to have a drop down box with a list of options for employees to choose from. If I do Data Validation and create the list for the cells in the calendar, it works. However, if I enter a choice from the list for Feb 10, it keeps that selection for the 10th of every month. How can I make my list in the calendar cells not repeat into other months? I'm not an excel expert and don't know how to figure this out without assistance.
14/03/2022 @ 01:54
Yes, because the calendar is in the same place ... every time you change the month from the drop-down menu, a macro is activated which deletes the data and changes the month according to your choice, but in fact it happens in the same place.
You can try like that... Make a calendar for the whole year, after just change the code like that
Sub Hide_Day()
Dim Num_Col As Long
Num_Col = 365 'The amount of columns needed for all months of the year
For Num_Col = 2 To 365 ' I assume the Calendar will start from B. You will need a Column for the Names of the Workers
If Month(Cells(6, Num_Col)) = Cells(1, 1)
Columns(Num_Col).Hidden = False
Else
Columns(Num_Col).Hidden = True
End If
Next
End Sub
07/11/2021 @ 06:41
macro is not working. Pls help
15/09/2021 @ 15:34
Many Thanks
27/08/2021 @ 15:20
Thanks for this, very clear and i succeeded in creating the calender. However how do I let the data keyed on the field do not disappear once the month change? Do you already have the data to share?
01/09/2021 @ 14:12
In fact, your question is more "how to save the value for each month". And the answer is "not possible with Excel". Have a look at Microsoft Planner in your Office 365 account.
06/07/2021 @ 12:13
Hi
let's say the calendar starts at 28th and ends with 27th. Ex. from 28th feb 2021 to 27th marts 2021 but the row continues to 30th, and i want to hide 28th, 29th and 30th.
is it the same code or do i have to change a little bit?
07/11/2020 @ 13:59
Great guide. I know this is an older post but having trouble with countif and dates of holidays. I am trying to make a calendar for scheduling during critical days and to quickly see if employee time off during holidays conflict with each other.
03/11/2020 @ 22:53
Hi, I'm having difficulty in the macro coding. it showing error *COMPILE ERROR* SYNTAX ERROR
Can anyone assist me with it ?
26/11/2019 @ 01:46
Hi,
Thank you for this tutorial.
Would you mind sharing how you go about to saving the data from month to month without it being cleared?
This template becomes extremely useful with the ability to retain data.
Thank you again.
26/11/2019 @ 15:02
Hi, Thanks to appreciate my article but I can't reply to your message just like that. It's a full development here because if you want to display the previous result, that means that each cells have formulas to collect the previous result (this formula is not easy and depend of the contexte of each worksheet). And of course, if you update one value in the calendar, you will delete the formula. I hope you understand the complexity of a such project. I can but it's a development (2 or 3 days for me)
31/12/2019 @ 01:02
Dear Mr. Frédéric LE GUEN
Thanks for this beautiful explanation
I followed your step-by-step explanation, and the result was beautiful, but when I return to a month in which the days are 30 days or 31 days, the days remain hidden.
Please, if possible, attach the calendar file. I will be appreciated you.
03/01/2020 @ 06:37
There is a problem with your macro for sure. Probably the row for your days is not the one mention in the code.
Have a look at this link to understand the code and also to customize it.
No, I don't share the file. It's not the way to learn how to use Excel
17/10/2019 @ 04:49
I have made change on the VBA code to make sure it run correctly as per bellow.
Sub Hide_Day()
Dim Num_Col As Long
'to unhide all columns
Columns.EntireColumn.Hidden = False
'This instruction cleans the content of the cells in your calendar
Range("B7:AF13").ClearContents
For Num_Col = 30 To 32
' Test if the month of the cells (row 6) is the same of the month selected (cell A1 or cells(1,1))
If Month(Cells(6, Num_Col)) > Cells(1, 1) Then
Columns(Num_Col).Hidden = True
Else
Columns(Num_Col).Hidden = False
End If
Next
End Sub
15/10/2019 @ 11:47
Based on this, can I populate fields below based on the month selection?
Additionally, can I use a vlookup or index match in another tab to pickup the entries despite this calendar being dynamic?
05/09/2019 @ 09:42
Hi!
Thank you for this , it's really helpful! 🙂
However the macro does not work for me. An error message appears (syntax error) for this row:
If Month(Cells(6, Num_Col)) >= Cells(1, 1) Then
Can you help me? What should I do?
07/09/2019 @ 10:15
Hi,
I have written this article all the explanation of the code. In function of the construction of your calendar, you have to adapt some arguments (like row number or column number)
26/06/2020 @ 10:30
Hi!
I have had same problem. Solution is following
If Month(Cells(6;Num_Col))=Cells(1,1) Then
That works
13/08/2019 @ 07:58
Hi, Could you also please share the code for to retain the data that has been input into cells when months are changed?
It would be really helpful !!
21/08/2019 @ 07:31
No because this is a full project with a lot of lines of code. Also, each situation is unique and I can't write a "standard" code.
29/07/2019 @ 17:27
Hi Frederic, I was unable to have the macro hide and unhide the columns (I rechecked and couldn't find the problem). I am also unable to retain the details in the individual months (whenever I return to a previous month, the macro cleans the contents). How can that be changed?
16/09/2019 @ 15:22
i was having a similar issue and changed my code on line 7 to this:
If Day(Cells(6, Num_Col)) <= "29" Then
works great
16/09/2019 @ 15:53
Actually what you need to do is change ">=" to ">" on line 7
01/07/2019 @ 17:03
excelent
26/06/2019 @ 00:29
Hi there, awesome video!! I would to like to keep the cells populated in each month and not have it clear the contents after selecting a new month / year. Is that possible and if so, please can you describe? Thanks in advance
08/07/2019 @ 05:14
I also have the same problem with this one. When I type in any cell through the month but the old content is still there. Please help. Thanks in advance.
13/06/2019 @ 21:14
Hi. Brilliant post, very clear and easy to follow, I have even customised it to my needs. But I need help to make a google spreadsheet version, any suggestions how to do that?
13/06/2019 @ 21:32
Thanks but Excel Online is better than Google sheet and free too.
All the steps are possible with Excel Online except the dropdown.
12/06/2019 @ 10:18
Hi,
Is there any way to keep the data just in that current month? I am using it to track employee sickness days and when you change the month it brings the data from lets say April into May.
Thank you.
Lauren
12/06/2019 @ 10:37
Hi Lauren, Yes it's possible but it's a development (macro and formulas) and it takes time.
Because, not only you have to manage to save each day of sickness and the way to display them in function of the month selected but also, you must manage an interface to update or delete previous data.
29/07/2019 @ 16:15
I also need the same function! Please make a tutorial to save the information under each month drop down!
26/06/2019 @ 03:33
The calendar is brilliant. But I wish to have this function as well so I can track sick leave/ vacation leave. Basically retain the previous month's data.
08/06/2019 @ 09:56
Is there anyway i can save the previous data when i switch to another month? For some reason when i come back to the previous month i lost all the data. Is there anyway i can fix this?
09/06/2019 @ 15:07
Yes it's possible but it's a real project with development. One or 2 days for me
05/11/2019 @ 13:08
Thanks for creating this calendar ...have you found the way to save the previous month data when selection next month.
Please guide
26/11/2019 @ 15:10
Hi, Thanks to appreciate my article but I can't reply to your message just like that. It's a full development here because if you want to display the previous result, that means that each cells have formulas to collect the previous result (this formula is not easy and depend of the contexte of each worksheet). And of course, if you update one value in the calendar, you will delete the formula. I hope you understand the complexity of a such project. I can but it's a development (2 or 3 days for me)
31/05/2019 @ 13:45
Can i download this calendar please?
31/05/2019 @ 21:23
Hi, it was possible to download it with Paypal long time ago but it was too difficult to manage the automatic download with their Platform. So now, it's not possible to download the file. Sorry
07/06/2019 @ 14:55
When I enter text in one month, it transfers to the next month - how do I stop this from happening?
07/06/2019 @ 17:51
I don't understand when you say "When I enter text in one month". You don't have to enter text but just select the month in the dropdown list
21/04/2019 @ 08:31
Great post!
31/01/2019 @ 09:56
Hello
I did the same as in exemple, except the macro couse in the first cels were you have link the mounth and the year i have to put the company name and adress wich is mandatory, so i had to move the linked the mounth and year to AG 1 and AG 2, and here is my problem, i try to change the macro but didnt work , may you please help me or tell me what to change in macro to work ?
Thank you in advance
Regard the rest of the stepts worked perfectly.
28/01/2019 @ 14:42
Hi Fred,
How can I add name in the title bar using Com Box? I have a list of people for each month
I want to show like this:
ELDRIN - from January 1, 2019 to January 31, 2019
Thank you,
23/01/2019 @ 10:00
Hi Mr. Frederic,
Could you please explain the code you've shared on Step 11.
As I change the month from January to February & February (to either of the month with less than 30 days) the hidden column will not appear anymore as it is always hidden even if I go to the month with 31 days.
I changed this code:
From:
For Num_Col = 30 To 32
To:
For Num_Col = 32 To 34
I used column from D7:AH70
I tried also the same code you've provided, still the same scenario.
Thank you,
02/01/2019 @ 18:31
Thanks a lot sir
I have found exactly what I want.
24/10/2018 @ 05:31
in this calendar format i want to add week number... is this possible? if yes please let me know how can i make...
31/05/2019 @ 13:48
Is your calendar reddy? Can i have a copy of it?
04/06/2019 @ 05:39
Sorry no. I don't share it. It's better if you try by yourself
29/09/2018 @ 16:22
I am still wondering if its possible to not clear the contents when moving the calendar forward.
I use the calender as a way to keep track of my employees vacation.
But when they type x in a date, lets says 1.7.2018. The x will then stay in that cell when i change month and year... Did you solve this?
Anyone please?
11/08/2018 @ 13:17
Hi good evening,
Thank you for providing such an wonderful article.
I tried my level best in preparing one of the daily update sheet for my employees based on the inputs provided by you. But when I am writing something in the month of January the same thing is repeating in all the months unchanged. How can I erase or hide the content of that particular month and start afresh in next month.
Kindly, help me in this regard.
Thanks
12/07/2018 @ 17:11
Dear Frederic,
Thanks for this fantastic video and help. I and wondering if its possible to not clear the contents when moving the calendar forward.
For example when my team enters their availability for July 2018 and August. Switching back to July all the contents clear.
14/07/2018 @ 18:39
The workbook doesn't save the previous values. To do that, it is compulsory to create a real program and it's not the purpose of the post.
24/07/2018 @ 15:16
Can you please share the code to public.
24/07/2018 @ 20:09
Which code ? It is in the post
31/05/2018 @ 13:41
Hi,
I have successfully made an automatic calendar (for meeting purposes) using your tutorial, however when I try to add the meeting title into the row below dates, in the correct date column it doesn't seem to change when the month changes it stays in the same position across the whole calendar.
I hope this makes sense could you direct me on how to fix it please?
Many thanks
05/06/2018 @ 10:24
I use the calender as a way to keep track of my employees vacation.
But when they type x in a date, lets says 1.7.2018. The x will then stay in that cell when i change month and year... Did you solve this?
The tutorial was great btw.
22/06/2018 @ 09:35
I'm also having the same issue! I've spent so long working on this and I cannot work out how to get this to work!
05/06/2018 @ 11:14
I have the same problem.
I use the calender to keep track on my employees vacation. But when they type in "x" in a given cell. It follows that cell when i change month or year.
Hope there is something we can do about this.
Many thanks for your website.
27/06/2018 @ 10:04
Hi, I have added the code to erase the content of the calendar when you change the month
28/06/2018 @ 14:46
Hi, thank you for the great template. I want to use it to book functions/events in the future, how can the macro be adjusted to allow me to move into different dates and years without loosing the information? At the moment when I move from June to July, all of the information in June disappears, removing the ClearContents instruction results in the information appearing in any month you select.
Many thanks for your time.
James
03/04/2019 @ 21:50
Hi Thank you for the post! It has been really helpful... I am having the same issue when changing from Jan to Feb... All of the information from Jan will past into Feb.
Thanks in advance,
Danielle
24/04/2019 @ 18:47
That's strange because the instruction to clear the contain of the range B7:AF13 is written in the code.
24/04/2019 @ 14:28
Hi Frederic, thanks for the tutorial. Is there something we can do to retain the data that has been input into cells when months are changed? I am using this as a communications planner and would like to be able to retain the data as we switch through months. Is this a possibility?
24/04/2019 @ 18:26
Hi Noelle,
Yes it's possible but it's a really program to manage new entries, change values, delete previous record and display previous month values.
For me it's minimum 2 days of development.
02/04/2018 @ 21:58
Hi There,
Many thanks for the tutorial.
I've made my own version but can't figure out how to do step 11 (Hide the last columns with a macro).
In my case I didn't make the calendar in columns, but in rows, and made a different layout than yours.
I can't figure out how to do make the macro so it can work on mine.
Could you help?
Thanks in advance
Bettina
11/04/2018 @ 09:03
Hi again,
I still haven't had a breakthrough with my previous answer. Could you please help me?
You can also write me an email, so you don't give away some of the answer for the paid part.
Thx again
01/04/2018 @ 10:20
Hi,
Let me start by saying that this is an excellent template and your instructions were quite easy to follow thanks for making it so detailed,
however I did have trouble trying to change the formula for the weekend highlight see where I am the weekends fall on a Friday and a Saturday can you advise what I should do in this case?
Thanks
19/03/2018 @ 15:01
The calendar does not automatically update the days within the months. I have tried to mess around with the formulas; however, everytime i chose a month from the drop down the days stay the same and nothing changes
19/03/2018 @ 15:10
There is no reason. If you have properly follow the explanation it works. Maybe your DATE formula is not linked to the dropdown cell.
Also, the solution is in the file you can download at the end of the article
16/03/2018 @ 11:31
Don't know if it's Excell 2016 or maybe because i use Danish version, but i had to replace , with ; in the formulas. 🙂
16/03/2018 @ 11:51
That's the setting of your computer. Control panel>Regional setting and there is one option to change ',' by ';' (or the other way round)
07/03/2018 @ 15:02
Hi There,
does this method for creating a calender automatically accomodate february when 28 days vs 29 days.
or what is the code to add to compensate and visibility when february is selected to only show the dates required for the full month?
20/02/2018 @ 05:06
It's superb and also i have created the same but if i will change the month data against the employee still the same how that will get change?
11/01/2018 @ 14:04
Hello Frédéric,
thank you for great calendar. I've added here also combo box with years. Unfortunately years are read as serial numbers.
E.g. my list of years starts from 2018, it got serial number 1, and is read as year 1901
Could you give me a hint how to solve that?
Thanks
11/01/2018 @ 03:09
Great Calendar and I have it working to high light weekends but have spent 3 days and can not get the stat holidays and macro to work is there a copy where all of this is built, I am not a technical IT type but I do try. Thanks
12/01/2018 @ 15:41
Hi Dave,
I have added the file. It is included in the pay-per-view part
08/01/2018 @ 18:47
Hi,
i really liked you design, can you share the template ?
09/01/2018 @ 19:28
It has been made from scratch and it's not downloadable.