Date Expert

How to make automatic calendar in Excel

Reading Time: 4 minutes

Step 1: Add a dropdown list

Insert a dropdown button in your worksheet

  1. Go to the menu File>Options
  2. Then Customize the Ribbon
  3. Check the developer option in the right pane
Add developer menu in the ribbon

Menu to insert a dropdown button for macros

Create a list of months

Automatic_Calendar_4

  1. Select your Combo Box object
  2. Right-click on it
  3. Select Format Control ...
Option for the dropdown button

  1. Select the Control tab (the last one)
  2. Select the range of month (in this example AH1:AH12
  3. 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
  • ...

The location of the Cell link is crucial for the rest of the explanation. It has in impact on the formula DATE and with the macro.

Step 2: Another Drop-down list for the year

  • Create a list of years in a column. It's just the value of the year, not a date (have a look at the data in column AI)
  • Insert a new drop-down list
  • Link this new drop-down list to this range of year
  • Select A2 as a Cell Link

Step 3: Create the first date in function of the drop down

  • The year it's the value of the cell link A2 + 2016 (yes, the first year in the list is 2017, so the result in A2 will be 1 and not 2017)
  • The month is just the value of A1 (easy)
  • And the day is 1 (first day of the month)

=DATE(A2+2016,A1,1)

Step 4: Extend the days

  • In C6, write the following formula
  • Copy the formula till the cell AF6

=B6+1

Step 5: Change the date format

  • Select all your dates (from B6 to AF6)
  • Open the Format Cells dialog box (Ctrl+1 or Home>Cell format>More Number format)
  • Select the Custom category
  • Enter ddd dd in the Type field.
Change the format of the dates

Step 6: Change the orientation of the text

  • Select B6:AF6
  • Activate the menu Home
  • Orientation
  • Rotate Text Up
Menu to rotate the wording

  • Select columns B:AF
  • Right click a the column header
  • Set the column width to 2.5

Step 7: Add color and borders

Step 8: Add a dynamic title

=DATE(A2+2016,A1,1)

=DATE(A2+2016,A1+1,1)-1

="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")

Step 9: Highlight the weekends

  1. Select all your data with the first row of your calendar (range B6:AF13)
  2. Create a conditonal formatting rule (Home>Conditionnal Formating>New rules)
  3. Select Use a formula to determine which cells to format
  4. Write the formula =WEEKDAY(B$6,2)>5
  5. Change the background color (orange in this example)

Very, very, VERY IMPORTANT ❗❗❗ There is only one $ in the formula after the column B

Step 10: Highlight the public holidays

  1. Select all your data (cells B6:AF13)
  2. Create a conditional formatting rule (Home>Conditionnal Formating>New rules)
  3. Select Use a formula to determine which cells to format
  4. Write the formula =COUNTIF(Holidays!$B$2:$B$4,B$6)>0
  5. Change the background color (red)

Step 11: Hide the last columns with a macro

ALL THE EXPLANATIONS OF THE MACRO CODE HERE

  1. Press Alt + F11 to open the Visual Basic Editor
  2. From the Insert menu, choose Module
  3. Copy Paste the following code in your module
Sub Hide_Day()
Dim Num_Col As Long
   '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

Step 12: Link the macro to the drop down objets

  1. Select the Combo Box
  2. Right-click on it.
  3. Choose Assign Macro
  4. Select the name of the macro (Hide_Day) and click OK

Step 13: Your calendar is ready

Automatic calendar finished

Related posts

Time Format in Excel

Frédéric LE GUEN

Excel as a sports coach

Frédéric LE GUEN

NOW & TODAY

Frédéric LE GUEN

76 comments

shashi 07/11/2021 at 06:41

macro is not working. Pls help

Reply
Hisham 15/09/2021 at 15:34

Many Thanks

Reply
Lilian 27/08/2021 at 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?

Reply
Frédéric LE GUEN 01/09/2021 at 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.

Reply
Malena 06/07/2021 at 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?

Reply
A 07/11/2020 at 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.

Reply
Haris Awan 03/11/2020 at 22:53

Hi, I'm having difficulty in the macro coding. it showing error *COMPILE ERROR* SYNTAX ERROR
Can anyone assist me with it ?

Reply
Exar 26/11/2019 at 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.

Reply
Frédéric LE GUEN 26/11/2019 at 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)

Reply
Hussain 31/12/2019 at 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.

Reply
Frédéric LE GUEN 03/01/2020 at 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

Reply
thuy 17/10/2019 at 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

Reply
Frederick Wright 15/10/2019 at 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?

Reply
Kitti 05/09/2019 at 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?

Reply
Frédéric LE GUEN 07/09/2019 at 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)

Reply
rubu 26/06/2020 at 10:30

Hi!
I have had same problem. Solution is following

If Month(Cells(6;Num_Col))=Cells(1,1) Then

That works

Reply
Deepika 13/08/2019 at 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 !!

Reply
Frédéric LE GUEN 21/08/2019 at 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.

Reply
Eteruza Medas 29/07/2019 at 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?

Reply
Meagan Lampton Gregory 16/09/2019 at 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

Reply
Meagan Lampton Gregory 16/09/2019 at 15:53

Actually what you need to do is change ">=" to ">" on line 7

Reply
Adriano Rodrigues 01/07/2019 at 17:03

excelent

Reply
Varun 26/06/2019 at 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

Reply
Kate 08/07/2019 at 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.

Reply
khomol 13/06/2019 at 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?

Reply
Frédéric LE GUEN 13/06/2019 at 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.

Reply
lauren 12/06/2019 at 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

Reply
Frédéric LE GUEN 12/06/2019 at 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.

Reply
Jay 29/07/2019 at 16:15

I also need the same function! Please make a tutorial to save the information under each month drop down!

Reply
Dante 26/06/2019 at 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.

Reply
WINSTON T CUISON 08/06/2019 at 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?

Reply
Frédéric LE GUEN 09/06/2019 at 15:07

Yes it's possible but it's a real project with development. One or 2 days for me

Reply
Tina 05/11/2019 at 13:08

Thanks for creating this calendar ...have you found the way to save the previous month data when selection next month.

Please guide

Reply
Frédéric LE GUEN 26/11/2019 at 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)

Reply
Gerrit 31/05/2019 at 13:45

Can i download this calendar please?

Reply
Frédéric LE GUEN 31/05/2019 at 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

Reply
Lorraine 07/06/2019 at 14:55

When I enter text in one month, it transfers to the next month - how do I stop this from happening?

Reply
Frédéric LE GUEN 07/06/2019 at 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

Reply
sandeep kothari 21/04/2019 at 08:31

Great post!

Reply
Micula Aurel 31/01/2019 at 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.

Reply
DRIN 28/01/2019 at 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,

Reply
DRIN 23/01/2019 at 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,

Reply
Om goswami 02/01/2019 at 18:31

Thanks a lot sir
I have found exactly what I want.

Reply
vineet singh chouhan 24/10/2018 at 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...

Reply
Gerrit 31/05/2019 at 13:48

Is your calendar reddy? Can i have a copy of it?

Reply
Frédéric LE GUEN 04/06/2019 at 05:39

Sorry no. I don't share it. It's better if you try by yourself

Reply
Maha 29/09/2018 at 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?

Reply
kiran kumar ganapuram 11/08/2018 at 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

Reply
Kara Anderson 12/07/2018 at 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.

Reply
Frédéric LE GUEN 14/07/2018 at 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.

Reply
Praful 24/07/2018 at 15:16

Can you please share the code to public.

Reply
Frédéric LE GUEN 24/07/2018 at 20:09

Which code ? It is in the post

Reply
Summer 31/05/2018 at 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

Reply
Stian 05/06/2018 at 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.

Reply
Michelle 22/06/2018 at 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!

Reply
Stian 05/06/2018 at 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.

Reply
Frédéric LE GUEN 27/06/2018 at 10:04

Hi, I have added the code to erase the content of the calendar when you change the month

Reply
James Layzell 28/06/2018 at 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

Reply
Danielle 03/04/2019 at 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

Reply
Frédéric LE GUEN 24/04/2019 at 18:47

That's strange because the instruction to clear the contain of the range B7:AF13 is written in the code.

Reply
Noelle Davis 24/04/2019 at 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?

Reply
Frédéric LE GUEN 24/04/2019 at 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.

Reply
Bettina 02/04/2018 at 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

Reply
Bettina 11/04/2018 at 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

Reply
Zeno Dsouza 01/04/2018 at 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

Reply
brooke 19/03/2018 at 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

Reply
Frédéric LE GUEN 19/03/2018 at 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

Reply
Frede Rahbek Jensen 16/03/2018 at 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. 🙂

Reply
Frédéric LE GUEN 16/03/2018 at 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)

Reply
Daniel Miller 07/03/2018 at 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?

Reply
Sumit 20/02/2018 at 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?

Reply
Lucie 11/01/2018 at 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

Reply
Dave 11/01/2018 at 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

Reply
Frédéric LE GUEN 12/01/2018 at 15:41

Hi Dave,
I have added the file. It is included in the pay-per-view part

Reply
shamik 08/01/2018 at 18:47

Hi,

i really liked you design, can you share the template ?

Reply
Frédéric LE GUEN 09/01/2018 at 19:28

It has been made from scratch and it's not downloadable.

Reply

Leave a Comment