«

»

How to make automatic calendar in Excel

 

Creating a new calendar each month is a waste of time, but many people do as they think they need to rebuild it to get correct weekends and public holidays.

But in fact, with few formulas, 2 conditional formatting rules, 1 dropdown list and 5 lines of VBA code, you can create an automatic calendar. Follow each of these steps 🙂
 

Tutorial video to create an automatic calendar

If you want to have an overview of this article, you can watch the following video. To avoid to waste your time, I have deliberately increase the speed of the video. But don’t worry, all explanations are in the following steps.

Step 1: Add a dropdown list

Let’s start from this worksheet where we have nothing except our employees’ name for the beginning.

Automatic_Calendar_1
First, in A1, we will add a drop-down list in order to select the month. To do this, you must have the Developer menu in your ribbon. If you don’t, activate this option in the menu via File>Options>Customize Ribbon, and check Developer in the right pane.

Now, select Developer>Insert>Combo Box

Automatic_Calendar_2
Then left click and drag your mouse to create a dropdown objet in your worksheet like in this picture.

Automatic_Calendar_3

Insert of the combobox in your worksheet in A1

 

 

 

 

 

Automatic_Calendar_4Now we need a list of month names somewhere else in our worksheet. In my case I need to place this list at least 32 columns away from my source data (maximum number of day per month + the column of the employees’ name), so I have used column AH (it’s the 34th column in Excel). Of course, if you don’t want this column visible for your users, you can hide it.

 

 

 

Automatic_Calendar_5Now, I’m going to link my Combo Box object with this list of month. It’s very easy as you will see.

1. Select your Combo Box object

2. Right-click it.

3. Select Format Control …

 

 

 

 

 

The Format Control dialog will open.

Automatic_Calendar_6

  • On the Control tab, go to the Input range text box and provide the location of the month list you created.
  • In the Cell link field, you need to link to the cell under the Combo Box (A1)

 

The cell link is the location where Excel will place the index number of the item you select. For instance, if you select May, (the 5th item in the list,) the value in A1 will be 5 and if you select September the value in A1 will be 9. So why place it in A1? It’s a trick to hide the linked cell output under the actual Combo Box. 😉

So now, we are able to link the selected month name with a number in the cell A1. If you want, you can also create another drop-down list to select the year.


Step 2 – Create an automatic date linked to the month and the year selected

In the previous step, we created a drop down list to select a month but we did nothing for the year. For this example we just need to enter the year in a cell (in A2 in this example).

Automatic_Calendar_7Now, we have to create a formula which will return the first date of the month based on the inputs in cells A1 (the month) and A2 (the year).

To do this, we have to use the formula DATE to calculate the first day of any month in fonction of the value in the cells A1 and A2. The value of the day in the formula must be equal to 1. As you can see on the pic, the formula in B6 depends on the result in A1 and A2

=DATE(A2,A1,1)

 

 

 

 

Automatic_Calendar_8For the other days of the month, the formula is even easier, as you just have to add 1 to the previous cell. In C6, write the following formula and copy it through column AF:

=B6+1

 

 

 

 

Step 3 – Change the date format and position in the cell

Automatic_Calendar_9We have already done a great job 🙂 Now we are going to focus on the date format. We already have the year and the month in the upper left corner, so we don’t really need them in our daily column headers. To do this, we can change the format of the date in the row 6 to display only the day. We want to display the short name of the day and also the figure.

1. Select all your dates (from B6 to AF6)

2. Open the Format Cells dialog box (Ctrl+1 or Home>Cell format>More Number format)

3. Select the Custom category and enter ddd dd in the Type text box.

 

 

Now let’s change the orientation of our date:

1. Select B6:AF6

2. Go to Home>Orientation>Rotate Text Up

Automatic_Calendar_10

Then, adjust the size of the columns B:AF to get this presentation.

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

Automatic_Calendar_11

Now, add some color and borders to your calendar. Pretty nice 🙂

Automatic_Calendar_12


81 comments

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

      James

      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
      ws.Range(“B7:AG19”).ClearContents
      Else
      ws.Range(“b7”).Select
      Exit Sub
      End If

      ws.Range(“B7”).Select

      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
    Craig

  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?
          Thanks

  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

      @Matthias
      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: