How to Make an Event Calendar in Excel - Part 1

Learn how to build an event calendar in Excel. You can store up to five events per day, and more than one million events in total. Plus, the calendar updates automatically based on your month and year selections.
This is part one of two.
PART TWO
• How to Make an Event C...
FINAL SPREADSHEET
spreadsheetlif...
COURSES
spreadsheetlif...
OTHER
Templates: spreadsheetlif...
Blog: spreadsheetlif...
Music by Epidemic Sound

[@ronvanlith]

Men that was easy !! Takes me 20 min🕰 and 2 coffe☕️☕️

[@spreadsheetlife]

That's awesome!!!!!! 😄🎉

[@albertvannikerk6665]

That is exactly what I was looking for. Thanks a million.

[@spreadsheetlife]

Awesome!! Super glad to hear it! Thanks for letting me know. 😄

[@ladyfingerhandmade]

I'm so excited! I'm planning on making a monthly meal planner with drop-down fields to pick from a list of recipes so this setup is PERFECT! The only thing I changed was the date formatting. I wanted the dates (numbers) for the previous and following months to be hidden so i had to add 2 formulas in conditional formatting, but it only took a second to figure it out. I literally did a happy dance in my chair when i was finished building the calendar lol. Thanks so much for the very helpful tutorial.

[@spreadsheetlife]

That is so amazing!!!! First of all, way to go on finishing the tutorial! Second of all, it's so awesome how you were able to change things to fit your specific situation too. Thank you for letting me know how it went. I love to hear stories like this, and it's incredible to know my video has offered so much value for you to use. 😊

[@danirami732]

Exactly what I needed. Thank you so much!

[@spreadsheetlife]

That's so amazing to hear. Thank you for watching! 😊

[@Arcade_Dave]

Awesome video and tutorial. This helped me with a project that I was working on. I do have a couple of questions. I want to be able to only show the current month and not the first few days of the next month. I do have a robust calendar of events so I need to not show the date numbers as well as the events for previous and future dates. Is that possible?

[@spreadsheetlife]

Yes, it is possible. As I showed in the video, you can add conditional formatting to change the font color of dates that are not within the selected month. To hide these dates, just change that font color to the same color as the background. For the events themselves, you can create similar (although a little more complicated) conditional formatting rules to hide events not of that month. Or you can change the formula to only pull events from the selected month. So you have options. If you want a little bit more explanation, it would be a lot easier to discuss via email. You can message me at [e-mail].

[@azks7254]

Hello, Thank you so much Your tutorial helped a lot to keep up with my work deadlines. Now if I want to list all of my upcoming projects deadlines throughout the year without having to delete the data I entered for each month that is passed How can I do that ?

[@spreadsheetlife]

My biggest advice is for you to watch part 2. It will show you how to add events to the calendar that dynamically update whenever you change the year or month. 👍

[@NinaPopovic523]

Great video! It is really helpful. I have a question which is the easiest way to enter events that last a whole month or longer?

[@spreadsheetlife]

Yay! I'm so glad this video was helpful!! 😊 That's a good question. Unfortunately with this design, you have to manually enter the event for each date that it's on if it is for more than one date. So add a row to the Events table for each date, enter those dates, and enter the event for each date.

[@pandharinathjoshi6565]

Excellent training your explanation is too good sir

[@spreadsheetlife]

I'm so glad that you enjoyed it! 😄

[@dauntlessarte6190]

This is what exactly what I was looking for. Thanks!

[@spreadsheetlife]

That's amazing!! I'm so glad you came across this video! 😄👍

[@lamaalzahrani3713]

Hello , Thank you so much this is really helpful , I just have an issue where we hide the date from rules I can see for example in Jan some dates related Feb in the end however not all of them are hidden only 3 and the rest they are visible can you please help in addition when I changed the month to Feb all details are hidden there so mush be something with the rule and I have followed exactly same function

[@spreadsheetlife]

Hmmm...it's hard to diagnose what exactly the problem might be without more context. If you'd like, email me at [e-mail], and attach your Excel file. I can then try to troubleshoot the problem for you.

[@VictoriaSilvester-p7h]

Hi, I wondered if you could assist please? I created the attached events calendar using your tutorial on KZbin (which is great by the way!) I'm sure I've added the formula's correctly but the dates are not showing on the first two columns in row 37 & 38 and when I click on a different month, no days/dates are populating at all. Can you help please? I've emailed you my spreadsheet.

[@spreadsheetlife]

I saw your email and sent you a response. 😊👍

[@KARENKEVIN16]

This is just what I was looking for! I do have one question. How can I set this up so that an event repeats every year? I need a calendar where the events are birthdays & anniversaries. I want to be able to type the list once and have it update the calendar when I change the year.

[@spreadsheetlife]

That's a great question. Unfortunately with this design, that isn't possible. You have to manually add the dates and events for each occurrence. Of course, I'm sure it is possible to modify the template to account for such events, but that would require complex changes unfortunately. I'm sorry that's the case.

[@wendellsolomons1636]

Thanks. An awesome video. Unfortunately, none of the formulas worked. What am I doing wrong?

[@spreadsheetlife]

Thanks so much! I'm glad you enjoyed the video. I'm sorry you are struggling with the formulas though. If you'd like, email me at [e-mail], and attach your Excel file. I can then try to troubleshoot the problem for you.

[@k-84130]

Awesome. Thanks a ton!

[@spreadsheetlife]

Thanks for watching! 😊

[@614kaman]

Thankyou so much. Your tutorial helps a lot. But I have a question on the formula -the weekday one. Could you explain the logic behind why it could magically hold the 1st Sunday of date? What does the (+1) mean at the end of the formula ? Thankyou❤

[@spreadsheetlife]

I'm so glad that you found this tutorial helpful! 😊 For your question, let me see if I can explain. So the formula in the video is: J5-WEEKDAY(J5, 1)+1. J5 contains the first day of the month based on the selected month and year. The WEEKDAY function looks at this date and gives us its weekday number (1-Sun, 2-Mon, 3-Tue, etc.). And so with these details in mind, here is how all the math works. Let's say J5 = May 1, 2024. This is a Wednesday. So the WEEKDAY function calculates the weekday number to be 4. So when we subtract 4 from May 1, 2024, this gives us Apr 27, 2024. This is a Saturday. So we add 1 to this date (the plus 1 at the end of the formula) to give us Apr 28, 2024 which is Sunday. And so, the math will always return the Sunday on or before the first day of the selected month, and this is due to the how the WEEKDAY function works in this formula. Hopefully this helps explain the logic a little bit better.

[@Bjorg20]

Hello Ryan, Thank you so much for do this organised calendar, I am struggling with the formula first one Text saying Left then =Date(C3 as etc I am unable to get that to work, what should they left bar say in formula form? When I do = and given formula it gives me error saying I need to put ' at the front, that doesn't work either...? Are maybe able to do the formula so I can copy it into my sheet...? Thanks a million for doing this for us amateurs

[@spreadsheetlife]

Hi there! I'm super glad you've enjoyed the video, but I'm sorry that you're struggling with the formula. To make it easier for me to help, send me an email at [e-mail]. Attach your file, and remind me what you're struggling with in the email.

[@Bjorg20]

@@spreadsheetlife I’ve just send you an email…🤞🏻

[@spreadsheetlife]

I got your email. You can expect a response here in a little bit!

[@Bjorg20]

I just wanted to express my deep gratitude for the extraordinary assistance you provided. Your valuable insights have truly opened my eyes to a fresh perspective, and the individual behind the KZbin channel is a remarkably humble person. I never anticipated receiving such overwhelming support from you. Your kindness and generosity are truly remarkable. Please continue to inspire others with your outstanding work. 👩🏻‍💻

[@spreadsheetlife]

You just made my day. Thank you so much for your kind and heartfelt words. They mean so much to me and really encourage me to keep going and give it my all to create the best content I can. 😊

[@amandabiggs1406]

This was one of the easiest tutorials that I have followed! Great job!

[@spreadsheetlife]

That's so amazing to hear! I'm so glad you found the tutorial easy to follow. Thanks for watching and for your kind words! 😊

[@precious816]

The formular match functions is given me an error message.

[@spreadsheetlife]

What does your formula look like? Could you paste it here for me to look at?

[@jamesfoster7595]

Great video. Two quick questions. Is there a preferred method to create a drop down list for the year(s) and is there a way to hide the events that take place in the next month from the current month calendar? I am working on a calendar for November 2024 with the month ending on a Saturday and the calendar has rows 37 to 42 that are not showing the dates for the first week in December but the events in the first week of December are visible. Is there a way to hide those dates for the first week of December while in the November calendar? Thank you.

[@spreadsheetlife]

Yes to both questions. To create a dropdown list for the year(s), you would add data validation to the year selection cell. Similar to what we did for the month selection. To hide events that are not in the current month, you can add conditional formatting to change the font color. However, this process is a little more involved. You are welcome to send me an email at [e-mail], and I can give a little further instruction on it if you'd like.

[@terrymartin979]

EUREKA! I figured it out! Thank you so much for making this video!

[@spreadsheetlife]

Thanks for watching! I'm so glad you enjoyed the video and also figured it out! 😄

[@Lara-sy7zp]

hi! is it possible if we make the second sheet vertical? like we'll make the reservations 1 and reservations 2 rows instead of columns? then the columns are: date, time, and name. something like that

[@spreadsheetlife]

Yes, that is certainly possible. If you'd like some guidance on how to set something like this up, you are welcome to send me an email at [e-mail]. 😊

[@QeysLucky]

Excellent teacher

[@spreadsheetlife]

😊👍

[@RearsilverGaming]

That was great thank you! The conditional formatting formula didn't work for me though so just created 2 rules, one for date occurring last month and one for next month

[@spreadsheetlife]

I'm so glad you enjoyed the video! I'm sorry you couldn't get the conditional formatting to work, but I'm glad you figured something out.

[@RearsilverGaming]

@@spreadsheetlife Keep up the great work!

[@spreadsheetlife]

I'll do my best! 😊👍

[@09thezodiac]

I had the same issue but when I went back into Conditional Formatting to edit, I pressed Stop If True for the MONTH(B7) Formula and it worked.

[@ladyfingerhandmade]

@09thezodiac oooh! I ended up making 2 formulas like OP, but I'll keep this tip in mind for the future. Thanks!

[@VISHNUMAYAJVISHNUMAYAJ]

Awesome🤩

[@spreadsheetlife]

😄👍

[@briand1529]

Great tutorial! I have the dates and days formatted however the dates don’t change with the month. Any suggestions?

[@spreadsheetlife]

If the dates are not changing with the month, then you want to double check your formulas. The issue must be there.

[@ChadwickGraves76]

good job... i appreciate your help

[@spreadsheetlife]

I'm so glad that you found this helpful! 😊

[@NajK-83]

Thanks alot,. Can you add hourly appointments (time input) and highlighted on the calendar?

[@spreadsheetlife]

I found your other comment for the part 2 video and gave you a response there. 😊👍

[@harshitmoolchandani6736]

Hey, amazing video. I don't know why my conditional formatting is not working with TODAY() as well as the Month formula.

[@spreadsheetlife]

Hmmm...it's hard to say without more context. You are welcome to send me an email at [e-mail] with a copy of your file. I can take a better look at it there.

[@Jknows1]

6:25 I am not getting the date , it showing some 5 digit number What should I I suppose to change ??

[@spreadsheetlife]

Dates are actually stored as numbers in Excel. This is why you are getting a number. To view the number as a date, select that cell and change the number formatting to date. I hope this helps.

[@renatokoller1326]

Hi Ryan, thank you for such a great video! Unfortunately the formula =DATE(C3,MATCH...) doesn't work in my excel. Do you have any solution for that? Thank you very much in advance!

[@spreadsheetlife]

Where are you located? If you reside in a country that uses a comma as the decimal separator for numbers rather than a period, then your Excel formula syntax may use semi-colons instead of commas as the argument separator. So try using semi-colons instead of commas. That might be the issue.

[@robertcabitto2701]

Did Ryan's solution work? I cannot get the formula to work for me

[@spreadsheetlife]

I saw your email and send you a response. 😊👍

[@aussiegutierrez1263]

@@spreadsheetlife Hi I am from Manila and the formula also does not work, have tried chamging tht commas to semi-colons :( help

[@spreadsheetlife]

Have you selected a month in cell B3? If not, this could be the cause of the error. If that still doesn't fix it, send me an email at [e-mail]. Attach your spreadsheet, and I can take a look at it for you.

[@charmainmogere556]

This is amazing 🎉🎉🎉 thank you

[@spreadsheetlife]

Yay!!! 😄👍 You are very welcome. Thank you for watching!

[@noureddinegdn3850]

Hey guys! is there a way to do the last steps on excel web? ( hiding dates that are not within the same month )

[@spreadsheetlife]

Hi there! Yes, the steps should be fairly similar. Of course, the Excel Web user interface looks a little different, but the functionality should be the same.

[@terrymartin979]

This is perfect and exactly what I need! However, I can't get the prior month to grey out. Do I type in MONTH manually, or is there a specific button I should click when writing the formula.

[@spreadsheetlife]

So awesome to hear!! 😄 As for the conditional formatting formula to get the prior month to grey out, you have to type in MONTH manually when writing this formula. And be sure that you have your references and the dollar signs correct as well.

[@titusautida5928]

Can i ask for part 1 and part 2?Sir❤ soft copy

[@spreadsheetlife]

Hi there! So you can access the end of the part one file via the description in the part two video. To get a copy of the finished product, use this link: spreadsheetlife.com/templates/event-calendar/

[@NicasioPineda]

Easy to Follow Great

[@spreadsheetlife]

So glad you found this easy to follow! 😄

[@TheBrownBalls1]

Thank you. I was doing good until I got to the conditional part. My Mac doesn't show the same options your video shows.

[@spreadsheetlife]

Hmm...interesting. The best way for me to help would be via email. Take a few screenshots, and share them with me at [e-mail]. Then we can go from there. 😊👍

[@stephanieflores9057]

Hi, is this available in google sheets?

[@spreadsheetlife]

I made a similar video for Google Sheets. You can find it here: kzbin.info/www/bejne/mKjGppqerrhohrM

[@camtule9612]

I accidentally pressed Ctrl ")" and the entire excel sheet got an error. Do you know how to fix it?

[@spreadsheetlife]

That's so strange. CTRL + ) should only unhide hidden columns. Can you describe your error in a little more detail?

[@camtule9612]

@@spreadsheetlife I don't know that command is used to hide the entire column. Searching many pages still did not yield results. Thank you very much. It's a pity that I spent a lot of time redoing a new file.

[@spreadsheetlife]

If you'd like, you can email your file to me at [e-mail]. I will do my best to take a look at it and see if I can fix the error.

[@camtule9612]

@@spreadsheetlife Thank you for your enthusiasm, I had already created a new file :((

[@spreadsheetlife]

Oh gosh, ok! Sorry I didn't get to your comment fast enough. I hope all goes smoothly the rest of the way for you though!

[@joedaniels242]

I’m having same issues with the first formula to get the date. =DATE(C3,MATCH(B3,J7:J18,0),1) I’m getting an N/A return when I hit enter.

[@spreadsheetlife]

Hmmm...if you are getting an N/A error...have you entered a year and selected a month yet? This might be the issue.

[@joedaniels242]

Yeah I figured it out. I hadn’t selected a month yet. Thanks for replying back. I got it all finished and love it. Thanks for sharing with us!

[@spreadsheetlife]

Of course! Thank you for watching! And I'm glad you figured it out! 😊

[@Bike_and_Life]

My cursor is not moving between the formula @11:16

[@spreadsheetlife]

Ae you trying to move the cursor with your mouse or the arrow keys? If one does not work, give the other a try. I hope this helps.

[@Bike_and_Life]

@spreadsheetlife i tried both. In the end, I have to type the complete formula again

[@spreadsheetlife]

I see. I hope you still got it to work even though you had to re-type the formula.

[@Bike_and_Life]

@ yeah, got it working, thanks a ton

[@spreadsheetlife]

Of course! I'm so glad you got it to work! 😄🎉

[@stacydescoteau4408]

I did the whole thing and some parts didn’t work for me. I sent it to you via email as you mentioned for others to do. Just wanted to make sure you got it

[@spreadsheetlife]

Hey there! I saw your email and sent you a response a few minutes ago. 😊👍

[@camilleechatman]

When I enter the DATE formula in J7, date reflected is month ahead. I am working on December 2024 however, it is showing January. Help!

[@spreadsheetlife]

Could you paste your formula here for me to look at? It will be a little easier for me to see what the problem might be.

[@joeroper3730]

How would I edit the formula to create a calendar to just show week days (Monday to Friday) thx

[@spreadsheetlife]

It would require a few different changes and adaptations to the formulas. If you send me an email at [e-mail], I can give you some ideas.

[@ΒασΚων]

I would like to ask something. When I write something in one cell of the calendar, and then change the month, it shows in the next one. That is, if I write something on January 10th, it will be there in February as well as in all the months. How can I write something in a cell, and it does not carry over to the next months? Thank you very much

[@spreadsheetlife]

Good question. See part 2! kzbin.info/www/bejne/kHeriYaXrt9_rKs In part 2, I show you how to add events that will also change when you change the month and year.

[@ΒασΚων]

@@spreadsheetlife in the last steps i type =INFA(IF(VLOOKUP(B7;Events;2;FALSE)=0;"";VLOOKUP(B7;Events;2;FALSE));"") ...but it shows me #ONOMA? (ONOMA is "name" in Greek). am i doing something wrong ?

[@spreadsheetlife]

If you are seeing that error, it may be a compatibility issue. Are you working in an older version of Excel? Change the IFNA function to IFERROR instead. Let me know if this fixes the problem.

[@ΒασΚων]

@@spreadsheetlife wow !!! yes !!! problem solved... thank you very much !!!

[@spreadsheetlife]

You're very welcome! I'm so glad that we figured it out!! 😄👍🎉

[@TinaCaldwell-m4u]

when I put in formula I get a NA , what should I do

[@spreadsheetlife]

Could you paste your formula here so I can take a look at it?

[@TinaCaldwell-m4u]

@@spreadsheetlife =DATE(C3,MATCH(B3,J7:J18,0),1)

[@spreadsheetlife]

Okie dokie. Your formula looks good. Have you tried swapping out the commas with semicolons? Depending on your location, this might be the cause of the error. If that doesn't fix it, send me an email at [e-mail]. Attach your spreadsheet, and I can take a look at it for you.

[@TinaCaldwell-m4u]

@@spreadsheetlife Ok will send it over, because the suggestion for semicolons did not work. Thanks in advance for your assistance.

[@spreadsheetlife]

I saw and responded to your email! 😊👍

[@intanmaristiarafuzylestari]

Thank you❤

[@spreadsheetlife]

You're welcome!! 😊

[@nqohshabangu488]

Thanks for the info, now assist on how to change the year because we are in 2025 now and this formular is showing 2024 date

[@spreadsheetlife]

To change the year, just select the cell containing the year, and type in the year you want which in this case is 2025. That's all there is to it! 😊👍

[@carol-annepeacock9480]

is there a way to colour code the events please?

[@spreadsheetlife]

Hi there! I found your other comment for the Part 2 video. I gave you a response there. 😊👍

[@chunwingoscarpoon2643]

Excellent! Thank you for your tutorial. Highly appreciate!

[@spreadsheetlife]

I'm so glad you enjoyed the tutorial. Thank you for watching! 😊

[@ValGianan]

Wow! Can u do this in google sheet?

[@spreadsheetlife]

Yes, for sure! Check out this video! kzbin.info/www/bejne/mKjGppqerrhohrM

[@jaacirajose]

the first formula is wrong, even using ; instead of , i stopped there

[@spreadsheetlife]

I'm so sorry you're struggling with the formula. If you'd like, post your formula here and I can take a look at it for you. 😊👍