Excel calendar [VBA]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The calendar sheet allows you to enter a year and use a drop down list to select a month.
A small VBA event code tracks which cell you have selected and shows the corresponding events accordingly.
How this workbook works
The animated image above shows how to enter data and how to select a given date. Today's date is highlighted yellow, days with one or more events are also highlighted, in this example blue.
Excel extracts data dynamically meaning the named range grows automatically when new data is entered, we don't need to change the formula cell references.
How I created the calendar
Table of Contents
- Calendar
- Conditional formatting
- Formulas
- Visual basic for applications
- Get excel *.xlsm file
Drop down lists
Select year
- Select cell B2
- Go to tab "Data"
- Press with left mouse button on "Data Validation" button
- Allow: List
- Source:2012,2013,2014,2015,2016
- Press with left mouse button on OK
Select month
- Select cell E4
- Go to tab "Data"
- Press with left mouse button on "Data Validation" button
- Allow: List
- Source:January, February, March, April, May, June, July, August, September, October, November, December
- Press with left mouse button on OK
Headers
Type Year: in cell B2, Month: in cell E2 and so on...
Calculating dates (formula)
- Select cell B5
- Formula:
=DATE($C$2,MATCH($E$2,{"January"; "February"; "March"; "April"; "May"; "June"; "July"; "August"; "September"; "October"; "November"; "December"},0),1)-WEEKDAY(DATE($C$2,MATCH($E$2,{"January"; "February"; "March"; "April"; "May"; "June"; "July"; "August"; "September"; "October"; "November"; "December"},0),1),2)+1
- Select cell B6
- Formula:
=B5+1
- Copy cell B6 (Ctrl + c)
- Select cell range D5:H5
- Paste (Ctrl + v)
- Select cell B6
- Formula:
=B5+7
- Copy cell range C5:H5
- Select cell range C6:H6
- Paste (Ctrl + v)
- Copy cell range B6:H6 (Ctrl + c)
- Select cell range B7:H10
- Paste (Ctrl + v)
Conditional formatting
Highlight Today
- Select cell range B5:H10
- Go to tab "Home"
- Press with left mouse button on "Conditional formatting" button
- Press with left mouse button on "New Rule..."
- Press with left mouse button on "Use a formula to determine which cells to format"
- Format values where this formula is true:
=B5=TODAY() - Press with left mouse button on "Format..." button
- Go to "Fill" tab
- Pick a color
- Press with left mouse button on OK
- Press with left mouse button on OK
Change font color for dates not in selected month
- Repeat 1-5 steps above
- =MONTH(B5)<>MONTH($B$6)
- Press with left mouse button on "Format..." button
- Go to "Font" tab
- Pick a color
- Press with left mouse button on OK
- Press with left mouse button on OK
Highlight days with events
- Repeat 1-5 steps above
- =OR(B5=INT(INDEX(Data,0,1)))
- Press with left mouse button on "Format..." button
- Go to "Fill" tab
- Pick a color
- Press with left mouse button on OK
- Press with left mouse button on OK
Formulas
Dynamic named range
- Select sheet "Data"
- Go to tab "Formulas"
- Press with left mouse button on "Name Manager" button
- Press with left mouse button on "New..."
- Name: Data
- Refers to:
=OFFSET(Data!$A$2,,,COUNTA(Data!$A$1:$A$1000)-1,3) - Press with left mouse button on OK!
- Press with left mouse button on Close
Event formulas
- Select sheet "Calendar"
- Select cell B13
- Array formula:
=IFERROR(INT(INDEX(Data, SMALL(IF(INT(INDEX(Data, 0, 1))=$G$2, MATCH(ROW(Data), ROW(Data)), ""), ROW(A1)), COLUMN(A1))), "")
- Enter this formula as an array formula, see these steps if you don't know how.
- Select cell D13
- Array formula:
=IFERROR(INDEX(Data, SMALL(IF(INT(INDEX(Data, 0, 1))=$G$2, MATCH(ROW(Data), ROW(Data)), ""), ROW(B1)), COLUMN(A1)), "")
- Select cell F13
- Array formula:
=IFERROR(INDEX(Data, SMALL(IF(INT(INDEX(Data, 0, 1))=$G$2, MATCH(ROW(Data), ROW(Data)), ""), ROW(C1)), COLUMN(B1)), "")
- Select cell H13
- Array formula:
=IFERROR(INDEX(Data, SMALL(IF(INT(INDEX(Data, 0, 1))=$G$2, MATCH(ROW(Data), ROW(Data)), ""), ROW(D1)), COLUMN(C1)), "")
How to create an array formula
- Select a cell
- Press with left mouse button on in formula bar
- Paste array formula
- Press and hold Ctrl + Shift
- Press Enter
Visual basic for applications
- Press with right mouse button on on Calendar sheet
- Press with left mouse button on "View code"
- Copy vba code below.
'Event code that is rund every time a cell is selected in worksheet Calendar Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Check if selected cell address is in cell range B5:H10 If Not Intersect(Target, Range("B5:H10")) Is Nothing Then 'Save date to cell G2 Range("G2") = Target.Value End If End Sub
- Paste to worksheet module.
Hide value in cell G2
- Select cell G2
- Press Ctrl + 1
- Go to tab "Number"
- Select category: Custom
- Type ;;;
- Press with left mouse button on OK!
Calendar category
This article describes how to build a calendar showing all days in a chosen month with corresponding scheduled events. What's […]
I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names […]
In a previous post I created a simple weekly schedule with dynamic dates, in this post I am going to […]
The image above demonstrates cells highlighted using a conditional formatting formula based on a table containing date ranges. The calendar […]
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]
This article demonstrates how to highlight given date ranges in a yearly calendar, this calendar allows you to change the […]
The image above shows a calendar that is dynamic meaning you choose year and month and the calendar instantly updates […]
The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]
This article demonstrates how to filter an Excel defined Table based on the selected cell in a calendar. The calendar […]
This article demonstrates how to build a calendar in Excel. The calendar is created as a Pivot Table which makes […]
Here is my contribution to all excel calendars out there. My calendar is created in Excel 2007 and uses both […]
What's on this page How to use this Excel Calendar How to add events How I built this calendar Worksheet […]
Sam asks: One more question for the Calendar that you have set up above can we have a excel formula […]
I have created another monthly calendar template for you to get. Select a month and year in cells A1 and […]
This weekly calendar is easy to customize, you can change calendar settings in sheet "Settings": Start date (preferably a Sunday or […]
If then else statement category
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to […]
Did you know that you can select all cells containing comments in the current sheet? Press F5, press with left […]
This article demonstrates how to use the If ... Then statement in Visual Basic for Applications (VBA). You can combine […]
Macro category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]
Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]
Today I would like to share with you these small event handler procedures that make it easier for you to […]
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from […]
This article demonstrates macros that save worksheets to a single pdf file. What's on this webpage Export all worksheets in […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
25 Responses to “Excel calendar [VBA]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
In the "Calculating dates (formula)" section, Step #2... you misspelled "January" as "Janauary" in the latter part of the formula.
Thanks!
[...] number formats, and much more. Home > Excel > Automate > Use a calendar to filter a table← Previous post - Use a calendar to filter a tableFiled in Automate, Drop down lists, Excel, Search/Lookup on Sep.10, [...]
[...] How to create an Excel calendar with VBA [Get Digital Help] [...]
hi,i am new to this blog.
but i hope i can get some help regarding a pop up calendar.
i found a pop up calendar for excel 2007,but when i try to use it in Excel 2010, the calendar does not appear. and i think it is related with the format behind.can you help me?thank you for any help and sorry for my poor English.
auni,
Do you have a link to the pop up calendar?
https://www.fontstuff.com/vba/vbatut07.htm
Hi, I would like to use this example with my data set however I'd like to visually show the amount of events per date to understand when are we the busiest, slowest, etc. and be able to forecast using this data. Ideally I would like some sort of data bar or color change indicating the level for each date (Jan 1 has 10 items while Jan 2 has 3 and I can visually see that in each cell instead of seeing numbers or a solid color for each cell (here yellow and blue).
Also, would it be possible to have Excel know the days of the month as in first 7 days of the month, last day of the month, every Monday of the month. I'd eventually like to get formulas set up to tell me on average how busy we are during these periods.
David,
Here is a basic example. It looks like a heat map.
Highlight dates VBA
Get the Excel *.xlsm file
Calendar-David.xlsm
Contact me if you want more features.
[...] David asks: [...]
Hi there, I have opened the above calendar - and want to add another filter so I can categorize the events/meetings in the calendar, e.g. Reg for regulatory, Ben for benefits - and so when you press with left mouse button on these it will filter for that type - is this possible to do? Appreciate your help with this.
Thanks
Rosie,
Yes, it is possible. Upload a file and I´ll see what I can do.
This looks great. I would like to use your calendar for a spreadsheet I am currently using. How could I just change where the event section refers to so it will automatically fill in the dates and times I need.
To give you context, I have a spreadsheet with many columns containing information. In these columns I have multiple dates/times for some rows. I want to create a calendar that references my spreadsheet with its events. I would like to keep all the information so that I can look at it still in the detailed view of each day (with all the information from the original row and also be able to reference the spreadsheet for multiple times for the same row but on different days).
How would I change the where the events are referenced to?
How could I add another option for dates so that I can add an extra column of values (I have the original dates column in the data sheet linked to another workbook?
[…] I got this codes from: https://www.get-digital-help.com/2012/09/05/excel-calendar-vba/ […]
Dear Sir
Please I would like to know how to highlight 2nd and fourth Saturday and all sunday as weekend. Can you please suggest vba code or formula.
advance thanks.
Yours faithfully
buvanamali
[…] How to create an Excel calendar with VBA [Get Digital Help] […]
Hi,
Thank you so much for the helpful template. This is awesome! However, when I choose January, it gives me all N/A values, can you please let me know what's going on? Thanks
I love this template. I am trying to find a way to be able to show student schedules, and I think I could use this but I would have to alter it and I'm not completely sure what I am doing. I have students that come to me for a 3 week rotation. I want a monthly calendar that will highlight the time each student is with me and then also allows me to input schedules for each day. Example student one is with me for 7/3 to 7/23, then on 7/3 they have orientation, on 7/5 they have conference and department meeting. Etc.
Emily
Have you seen this calendar?
https://www.get-digital-help.com/2016/11/10/calendar-monthly-view/
I think it will suit your requirements better.
Hi, your calendar is great for me. I want to add a new column D in the Data sheet and show the details in the calendar sheet (I added a new column right after the "Text:", how can I revise the array formula to show it properly?
Hi, I would need a help on case where event lasts for more than one day. I've managed to fix conditional formating so that color is shown well in callendar however I'm stucked on event formulas. I'd like to show first events which are from previous day and then current day ones. Could you advice how to build these table formulas correctly?
Thanks for share, Works great!!
Thank for sharing! I was looking something like that days now!
is it possible when highlight (choose a date) it will open a new sheet? in order to enter cashier data?
And highlight red that date if all the info from the above table are completed?
The table is look like that
https://i.postimg.cc/vBxZqxcp/2021-06-22-231754.png
Also if it is possible new tab will named as the date.
Hope you understand what I am saying. (Apologies, I am Greek so English are not my native language)
Thank you!
Hi Oscar,
I am no bright light with pivot tables but I have managed to create an excel calendar (vba) and it works great!!! Thank you so much!!!!
I have just one question as so far I have entered ones off events in it but I would like to also add recurring events (every monday there is a meeting). Short question: is that possible?