How to make automatic calendar in Excel

Step 4 – Add a dynamic title

Next, we should provide a descriptive title for the calendar that lists the period selected; a title where you can read the first and the last day of the selected month. To do this, we have to mix the formula to display the first day of the month and also the last day of a month, and also, control the format of the date with the TEXT function.

Formula for the first day of the month is:


Formula for the last day of the month is:


So, merge cells B4:AF4 and write the following formula where you link the 2 previous formulas, with the function TEXT.

=”Period from the TEXT(DATE(A2,A1,1),”dd mmmm yyyy”) &”to the “&TEXT(DATE(A2,A1+1,1)-1,”dd mmmm yyyy”)

And the result is:



And each time you change the month is the dropdown list, the title will be updated automatically.

Step 5 – Highlight weekends and holidays with conditional formatting

Now, here is the most interesting part of the work ; highlighting the weekend and the public holidays with a different color automatically. In this previous article on the Office blog, I gave you a lot of examples to highlight dates and gap between dates is a worksheet. To build our automatic calendar, we need to create 2 conditional formats. You can refer to this article if you want more details about conditional formatting.

Automatic_Calendar_141. Select all your data (cells 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)





For the public holidays, you’ll need to add the public holidays of your country in a new worksheet.


Then, repeat the same 5 previous steps

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)


When you open the Conditional formatting manager (Home>Conditionnal Formatting>Manage Rules), you can see the 2 rules in the selected area of your worksheet.


And now your calendar automatically changes the color to reflect weekends and public holidays.


Step 6 – Hide the last columns with a macro

Because of fact that all months don’t have the same number of days, we have to create a short program to hide the last column(s) of your calendar. The program is very simple. We just have to loop through columns AD, AE and AF and check if the month value equal the value in A1 (value of the month selected). If it’s not the case, columns should be hidden.

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
For Num_Col = 30 To 32
If Month(Cells(6, Num_Col)) <> Cells(1, 1) Then
Columns(Num_Col).Hidden = True
Columns(Num_Col).Hidden = False
End If
End Sub

Automatic_Calendar_18Now, to finish, you have to link the program with the drop-down control. That way, each time you change the month, the program will run and will hide/show the column(s) as necessary.

1. Select the Combo Box

2. Right-click it.

3. Choose Assign Macro

4. Select the name of the macro (Hide_Day) and click OK







THAT’S IT 🙂 Now, each time you select a month in your drop-down list, your calendar will automatically change the color of the weekend, holidays (providing your list is up to date, of course) and also hide columns as necessary.


3 pings

Skip to comment form

  1. Sally Heron Christensen

    I love this, thank you so much 🙂

  2. Ernest S. Doles

    Hi, it is possible to use only “d” as a customized variable

  3. delphina86

    Great tutorial, but I don’t understand what the purpose is to be able to change month if the cell values don’t change with it…(same request above by Kim and Lino), anybody has a solution?

  4. Kim

    can’t get Countif to work either and I can enter input data without it repeating on the following months either. Does anyone know a solution to this?

  5. Sachin

    Hi ,
    I am getting an error in Title , i am not sure about why , i am getting an Error with “DD” Please help

    =”Period from the TEXT(DATE(A2,A1,1),”DD mmmm yyyy”) &”to the “&TEXT(DATE(A2,A1+1,1)-1,”dd mmmm yyyy”)

    1. hihithere

      =”Period from” & TEXT(DATE(A2,A1,1),”dd mmmm yyyy”) &”to the “&TEXT(DATE(A2,A1+1,1)-1,”dd mmmm yyyy”)

  6. Angie

    Lino can I know if your issue is resolve? Would be interested to know

  7. Angie

    What good is a calendar if you cannot input any thing such that it keeps repeating the same data in same cell in Jan and the following month.I agree with Kimmier. Unless someone has a solution.

  8. Emy

    I am having problems with the public holidays as well. Apparently, the holiday dates I entered are not being recognized as matching the calendar. I am not sure what I am doing wrong.

  9. Charo

    Couldn’t find Developer in my 2007 Excel version until I found these instructions: Open Excel, Click on the Microsoft Button, choose Popular, Select Show Developer tab in the Ribbon, Click OK to close the Options dialog box.

  10. Lino

    Hello there!
    I`ve done it and worked just perfect! Thanks!

    Now I need something else.

    I choose one month (e.g. January) e fill some cells, it works fine but when I change the month (e.g. March), all the values in the cells remains. I need to start another month with blank cells!

    Thank you!!

  11. Rotha

    I cannot make the right macro to hide column. Can you please help?

  12. Wendy

    I cannot get COUNTIF to work, which means that I cannot highlight public holidays. I don’t know what I keep doing wrong. When I enter the formula it either highlights the whole month of January, or it highlights all other months apart from January. Please help

  13. John

    I found this very helpful. At the bottom of the schedule, i have added cells to calculate the number of different types of shifts for each day. I would also like to highlight those cells based on whether or not the required amount of each shift has been met. I need to format these cells with day of week in mind in addition to a certain number goal. I need help with this please.

  14. gsw

    Hi Frederic, I would like something same as described in Tyler’s message, can you please show us how to implement it?

  15. Tyler

    This is a cool calendar, but how would I alter this to have the information that is input into the cells for each employee saved for each month? It would be nice to be able to go back a month and be able to see what that information was, likewise, when I move forward a month have the information for each employee cleared for new input. There must be some way to do this so I can look at historical data and also plan ahead without having to delete any information.

  16. Anonymous

    Great tutorial! Is there any advice on allowing for Sunday to start each week instead of weekdays changing w/ date?

  17. Linda

    Thank you for this! It was a fun exercise and everything worked out just as demonstrated!! Is there the step by step to add the hide day macro? I tried this from pausing the video, but it didn’t work out for me and I’d love to learn how to add that feature.

    1. Verdant

      Follow steps 1-3 to enter the macros. Click out of the module by clicking the red X (in the upper right). Next, right click in the combo box (in A1) then continue following steps 2-4 to link the drop down combo box with the macro. Go to the Developer tab, click on Macros, click on Hide_Days, click Run (causes the macro to run). Then try changing the month in the combo box drop down (in A!) and you’ll see that February 2015 for example is now correct.

      1. Eva

        Verdant, excellent advice! It worked! Thank you.

  18. Mona

    Can I somehow write in the fields?
    When I write an X in a field, it will stay in that cell all months/years.

  19. Anonymous

    I created Year and month as a drop down list field. Then I tried to use the Date function. But it is giving a “Value” error.
    Can you please suggest why is that and how can I sort this out.

  20. Shay

    How do I get Feb 29 2020 to show up? It will be a leap year like 2016. Also, Feb 2015 still shows Sun 01 rather than just ending at Sat 28.

  21. Silvertrain

    If I a number in one of the cells next to an employee to mark it as holiday, it remains there even if I change the month. How do I retain the entered numbers for each different month?

  22. Lauren

    Hello Mr. Le Guen, thanks for the excellent tutorial. I did not recreate this exact calendar, because my purpose it somewhat different, however, I did use most of the functionality you used. I have only one question, and that is with regards to the indication of the holidays. It works perfectly for the year 2015, but if I change the year to 2016 the calendar doesn’t show any holidays anymore (similar for other years). For your information the cell that gives information about the year is B1, and the user just needs to give in the year in that cell. The cell that gives information about the month is cell B2 and there the user just needs to give in 1 for January, 2 for February etc. Would you have any idea how to fix this?

  23. Anonymous

    Hi, Can i know how to set Saturday as Working days. Thanks for advise.

    1. Lauren

      Hi! Just use >6 in the Weekday formula and it should only indicate Sundays as weekend-days. What this formula does, is that it assigns a number to every day of the week, Monday=1, Tuesday=2 etc. With the parameter >6 you’re telling the conditional formatting to only format the cells that have a number bigger than 6, so that’s only Sunday 🙂

  24. david s.

    hi, i am wondering if anyone is able to help me with this…
    instead of the following horizontal display of dates as shown, i had my dates displayed in a vertical way.
    obviously, the coding will not be able to hide the dates. as such, i am wondering how should i modify the coding to suit the vertical display of dates to hide the dates.

    thanks in advance for any advices.

  25. Anonymous

    That is what i need. Thanks a lot.

  26. Angie

    I created this calendar you provided. I need to add 3 additional columns and not sure how best to do this.
    I would like to add “Total 2016 Entitlement”, “Total Take 2016” & “2016 Balance” because I want to use this calendar to keep track of peoples vacation time.
    If Employee 1 has 23 days entitlement and through out the year they have marked in 10 days taken, the balance should be 13 days.
    I’m haven’t used Visual basics much at all and I’m thinking this is probably the best way to do these calculations and keep track of the time.
    Can you assist me?

  27. Biswajit

    I am beginner in excel tools, it really easy to creat for specially leave planner. I am unable to update leave details in main sheet (Like-L – Leave or A – Absent). Actually once months is getting change, my inuts also should change. Can any one help on same. Thanks- Biswajit

  28. Anonymous

    Ita takes approximately 30 minutes to create this calendar yourself. Im beginner in excel and it took me 30 minutes.

  29. lossos winkelmann

    could you publish a template of this calendar which i could just open in excel? I am to new and it would t ake me hours to work myself into excel to create his calendar but i need one like this – or can you tell where I could download something like this? thanks

  30. Kimmier

    This calendar is not useful to me AT ALL! I dont understand why the creater of the calendar won’t help with the solution to the problems. If I enter a number in a cell, that number carries on to the other months….thats crazy!

  31. Rekar JAwhar

    thanks a lot. its really very useful for me.

  32. Dardan

    Hi, Thank you for such a good tutorial, but the only issue that I’m having is I can’t add the Holidays day! I’m following the exact steps that you showed but it doesn’t work!!! I’m using excel 2013. Please help I would really appreciate.

    Thank you.

  33. Anonymous

    I have the same problem as Kimmier and John Any suggestions anyone?

  34. Bruce Smith

    I cannot get past step 4. I have Excel 2013 and when I put that formula in it gives me and error “We found a problem with this formula. Try clicking Insert Fucntion on the formulas tab to fix it, or click help for more info on common formula problems. I thought I had made an typo but finallly I copied and pasted your exact formula in and got the same error. I cannot make it work. What is the deal?

  35. sachin k

    Thanks a lot. its really very useful for me.

  36. Diogenes Ballesterosij

    What if my weekdays are Friday and Saturday?

  37. Diogenes Ballesterosij

    This is really awesome!

  38. Kimmier

    When I enter a number in a cell for one month, that same number carries over to all the other months. Is there a solution to fix this problem?

    1. john reposa

      I am also having this problem. If I add any values it carries to each month. How can I get it to not carry over?

  39. Kimmier

    Do you have a solution on how to fix this problem?

  40. Al

    In the last post it should of read What formulas should I use to hide rows …not hide columns

  41. Al Shepherd

    I am trying to make the calendar in a verticle orentation With dates down the B column starting at B5 thru B35 and employee names C5 thru G5.
    What formulas should I use to hide coulmns and color weekends and holidays.

  42. James Mylott

    I think it’s a great tutorial really helpful, but like a few others I need to input data for each month on certain days, it would be great to have a further tutorial to explain the clear data VBA code.

    1. Kevin


      Something like this would work. I inserted a shape on the top of my calendar and assigned a macro. The code below will ask if you are sure first, then based on answer delete all input data or just go to cell B7 and exit sub.

      Sub ClearCalendar()
      Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets(“Calendar”)

      ans = MsgBox(“Are you sure you want to clear all calendar content?”, vbYesNo, “Clearing all Content”)

      If ans = vbYes Then
      Exit Sub
      End If


      End Sub

      thanks kevin

      1. Tyler

        This is nice, but is there code that will allow me to save the information for that month before I clear it. That way I can go back to a previous month and see the information?

  43. Kevin

    #Name usually indicates a spelling error in a function name. Make sure you are using DATE and TEXT

  44. Dylan

    Hello, I have run into a problem along step 4, whenever i try to enter the formula i get a #Name error?, how can i solve this?

  45. GD

    Hi Frédéric LE GUEN, Thanks for sharing such a valuable post and information about automatic calendar

    I need little HELP

    i’m getting some error in 5th step part(b) in holidays adding

    ERROR {You may not references to other work books for conditional formatting criteria}

    please tell me what is wrong here from me.

    for holidays in new worksheet I have entries from cell B2:B9. Kindly inform me where i’m doing wrong or made a formula for me calender is in same same cells and columns and holidays entries i have mention above

  46. Shreekantha

    thanks a lot boss: it works awesome:

  47. craig

    Hi Frederic,
    Thank you for sharing your knowledge and skills in building this calendar, its great to share free knowledge. I would like to ask if possible how i would use the macro when i protect the worksheet? When protected the error message. ‘ runtime error 1004 Unable to set the hidden property range class’ is displayed when i choose another month form the drop downlist. i have tried a few things in the macro code but cant seem to nail it, i think its the hidden cells causing me grief. any ideas?

    thank you

  48. SIon

    Hello, I have created a Holiday planner using the format provided, but unfortunately when I input data, for example employee 1 has the first week in September off that same information is shown on every month, do you have any ideas how I can select each month in turn and the information is correct, thanks for any help

    1. Frédéric LE GUEN

      The tutorial present the way to create an empty calendar to avoid to redesign a calendar each month. It’s not a tutorial to record the value for the different month. If it’s what you want, you need to link it to a database but it’s a more complex job

      1. adnan kazi

        the same problem evn i m gng through can v solve dis? in anyway

        1. dhillon

          any one with solution, how to do this?

  49. TonS

    It’s working for me now. I had an extra row. So changing “Cells(6, Num_Col)” into “Cells(7, Num_Col)” worked for me.

  50. TonS

    The hiding works for me, but not the unhiding when a month has 31 days.

  51. Dharmesh Rajput

    It is a brilliant tutorial and works fine for me even in the leap year. Thanks for sharing the knowledge.

  52. Tony

    In the last screen shot above in column J2 it is showing FALSE, where has this come from? Is this something to assist with the macro as per the first comment maybe?

  53. puffyrozz

    Hey guys, when we edit the Cells , to add work shifts, if for e.g I edit a cell Ist June 2015, the same cell is being edited in August automatically as well. But I need to be able to edit each day individuallly. Thanks for your help

  54. Kevin

    This was a great idea. I was able to make the holidays change from year to year using the weekday and date functions. For example President’s Day in the US is always the 3rd Monday in February each year. To calculate that I used… =DATE(A2,2,1)+7-WEEKDAY(DATE(A2,2,1),3)+14

  55. Matthias

    First I wanna thank you for the great Video. The calendar itself works great. I have just one Problem. When I fill in some Content in let’s say january and Change to feburary, the same Content appears. Why is that, could you help me there? Thank you a lot. Regards Matthias

    1. Kevin

      There is no code to remove any existing content in calendar. You would need to add some extra code to make that happen. Like range(“B7:AG19”).clearcontents (of course you would have to adjust the range, and tie it to some other event).

  56. Steven

    i’m also facing the same trouble at last step too…..pls help me

  57. KP

    i am unable to change data for different months. the fields from B6 to AF13 remain the same. COuld you please help?

    1. Frédéric LE GUEN

      Do you link your cell in B6 to the cell of the dropdown list?

      1. Anonymous

        May be i am not doing it right. The months are changing. But say i input some data for employee 1 like 123 in Jan 01, 354 in Jan 2 and so on. Even it i change the month, the data remains the same. Ideally, the sheet should be blank for February.

      2. KP

        May be i am not doing it right. The months are changing. But say i input some data for employee 1 like 123 in Jan 01, 354 in Jan 2 and so on. Even it i change the month, the data remains the same. Ideally, the sheet should be blank for February.

        1. Tyler

          Anybody ever get an answer how to do this?

  58. Adwitiya

    I’m facing the same trouble as Ayashi.

    1. Ayashi

      Code works if dropdown list in at Cell A1. Try check if your code is correct.

  59. Ayashi

    I have some trouble with the last step of hiding columns as necessary when click on certain Month from the drop-down list. It always hide Column 30-32 by default, i.e.: even for months like Mar / Apr … so my last columns becomes 28th for any month being selected.

    1. Frédéric LE GUEN

      Impossible. I have test this code many times and it’s the same code in the video and it works

      1. Ayashi

        Any possible errors I’m facing that you may guess perhaps ?

        1. Ayashi

          Rectified. Code works if dropdown list in at Cell A1. Mine was initially at A3.

      2. Kevin

        The VBA code you wrote works fine for me.

    2. Kevin

      To Ayashi, Are you sure the code is referring to the correct cells for the date? If you followed Frederic’s example “cell for cell” everything works. If you have strayed from the example in the video or website then you would need to adjust the cells(r,c) address. 🙂

      1. Ayashi

        Hi Kevin . Yes . I strayed cell A1 to A3 . I have replaced the code accordingly . All works fine now .

  1. drop down calendar problem pls help

    […] = window.adsbygoogle || []).push({}); I have a drop down calendar I created from this website How to make automatic calendar in Excel When I enter a number in a cell for one month, that same number carries over to all the other […]

  2. Automatic Cell Formatting Question ( Conditional Formatting )

    […] I'm using the same thing I was told to on this tutorial when I started making the first map Automatic Calendar Tutorial Step […]

  3. Automatic Calendar

    […] || []).push({}); Hi all, I created an automatic calendar following this website How to make automatic calendar in Excel to hide the last column of the date where there are lesser day in a month, the following coding […]

Leave a Reply

%d bloggers like this: