How to Build a Dynamic Calendar in Google Sheets

In this video, I teach you how to build a dynamic calendar in Google Sheets from scratch.
What's amazing about this template is that you can select any month for any year, and the calendar will automatically update all by itself. On top of that, this calendar is perfect for printing either in landscape or portrait orientation.
Join me in building this amazing template from scratch!
TEMPLATE LINK
spreadsheetlife.com/templates...
RESOURCES
- Spreadsheet Templates: spreadsheetlife.com/templates/
- The Spreadsheet Life Blog: spreadsheetlife.com/blog/
LEARN EXCEL OR GOOGLE SHEETS
- Excel Course: spreadsheetlife.com/excel-lau...
- Google Sheets Masterclass: Coming soon...
MUSIC TRACK
Track: Ikson - Still [Official]
Music provided by Ikson®
Listen: • #46 Still (Official)
TIMESTAMPS
00:00 - Intro
01:10 - Build the Template
09:41 - Conclusion
10:17 - Outro

[@KathyWoodward-jf3wm]

After spending hours watching 2 other videos while trying to implement what they were saying I was about to give up. I then found your instructions and they were WONDERFUL! I am a total beginner and I was able to create a great calendar thanks to you.

[@spreadsheetlife]

That's so awesome to hear! Thank you for letting me know. There's nothing better than finding out my content has been helpful to others! 😊👍

[@karinkott3885]

Crystal clear instructions. Thank you so much!!!

[@spreadsheetlife]

You are so welcome! I hope you enjoy the calendar, and thank you so much for your feedback. 😄

[@elmercartoneros560]

You are amazing! Easy and understandable. All your videos are informative and applicable to me. Million thanks 🙏🏻

[@spreadsheetlife]

Thank you so much for your kind words. I'm so happy to hear that you find my videos both informative and applicable to you! Thank you for both your feedback and support.

[@lenamccain4399]

Thank you so much for this! I've been trying to figure this out for what seems like forever. Really appreciate it!

[@spreadsheetlife]

Thanks so much for sharing! I'm so glad that you found this video and were able to benefit from it.

[@lenamccain4399]

@@spreadsheetlife Another thing I'm trying to figure out is how to make my weekly calendar autopopulate the dates from the monthly dynamic calendar with a dropdown as well. Any thoughts?

[@spreadsheetlife]

Hmmm...sounds like an interesting idea. Send me an email, and I may be able to give you a few thoughts on it. spreadsheetlife.com/contact/

[@ibrahimbhalfas8195]

Your directions is so amazing very clear and definitely very helpful. Jazakallah khair brother!

[@spreadsheetlife]

Thank you so very much. I can't tell you how amazing it is to get feedback like this. Thanks for watching, and I'm so glad you enjoyed the video and were able to follow.

[@ShyMartinez]

Thanks immensely for this!!!!

[@spreadsheetlife]

You are so very welcome!! 😊👍

[@nileshbaglekar7490]

Thanks a lot brother! Great work.

[@spreadsheetlife]

You're very welcome! Thanks so much! 😊👍

[@branscsmsbc7522]

omg... I've been looking this for weeks. You're so amazing. It was fun, so easy to follow, and you explain so well. THANK YOU SO MUCH.

[@spreadsheetlife]

Thank you so much for you kind words! I am so glad that you enjoyed the video, and I hope you absolutely love using the spreadsheet!!

[@bijaymohan]

superb - beautifully created

[@spreadsheetlife]

Thanks so much!

[@user-ql7ng1xr3w]

amazing! thank you very very very much for your amazing video

[@spreadsheetlife]

You are very welcome! I am so glad you enjoyed the video! 😄

[@user-zb8sf3dw3c]

IM SO HAPPY I FOUND THIS VIDEO

[@spreadsheetlife]

I am too! Thank you for watching!! 😊

[@gek-gek]

Thank u. I subscribe.❤

[@spreadsheetlife]

Thank you so much for subscribing! I'm glad you enjoyed the video.

[@jasminedawkins875]

Do you have a video on how to add additional rows( add row below row 6) without it changing the date formula for each cell. Trying to add rows beneath dates for an Event space to just type events happening for the day.

[@spreadsheetlife]

I do not have a video on this yet. But I've gotten several similar requests, so I'm planning on making one in the future.

[@meaganlong2055]

This was so fun and easy to build! Your directions are so clear and easy to follow! Could you explain how the formula in cell B6 works? I feel like I'll be able to use that in future projects for sure!

[@spreadsheetlife]

Thank you so much for your feedback! As for the formula in cell B6. This formula uses the SEQUENCE function to generate a 6 x 7 grid of numbers due to the first two arguments. The third argument of this function specifies the start value, and this is where things get a little confusing. The start value is calculated by taking the referenced date value (the first day of the selected month and year) and subtracting the weekday number of that date. For instance, if the referenced date value happens to be a Tuesday, then the weekday number for that date is 3. (1 - Sun, 2 - Mon, 3 - Tue) After that, a one is then added to the calculated value to give us back the first Sunday on or before the referenced date value. I hope this helps you to better understand the formula!

[@andreabolldorf675]

@@spreadsheetlife Thank you for this explanation. I do understand it, but still keep struggeling to make it work for the week beginning with Monday and not Sunday. Would you mind giving me a helping hand and create this formula for the cell B6? Thanks a lot in advance.

[@spreadsheetlife]

I'm sorry I'm just now getting to this comment. I totally missed it earlier. Anyway, try this formula. =SEQUENCE(6,7,H13-WEEKDAY(H13)+2) I hope this helps!

[@khairinakhairudin5275]

hi, your tutorial really help me so much, but I change the layout according to my preferences. however for conditional formatting it doesn't work, I try looking up another solution, unfortunately it didn't work. hope you can help me. thank you so much!

[@spreadsheetlife]

Yes! Send me an email and I can help!

[@arandomuser188]

Amazing tutorial! Perfect for creating my own Planner, but I did encounter a problem; When trying to follow your Conditional Format Rules bit, for some reason it will select all the tabs, like I couldn't get it to make it different (select the days that are not on the month) Is there a way to fix it?

[@spreadsheetlife]

Yes. It's usually easiest for me to help via email to diagnose specific problems. I encourage you to email me at [e-mail], and I can help from there.

[@rodrigocastilho9455]

Thanks for the video! It helped a lot. I was wondering if it is possible to add notes to the days before printing? That'd help a lot! Cheers

[@spreadsheetlife]

I'm so glad this video was helpful. If you are looking for a calendar where you can add notes to the days, check out this video. kzbin.info/www/bejne/mKjGppqerrhohrM

[@cpiva794]

Hi! Thank you for the video!! One question, how can I add the number of week to the right?

[@spreadsheetlife]

Hi there! You've very welcome. As for your question, could you explain a little more of what you are trying to do? I'm sorry, I don't quite understand the question.

[@VanitysGunn]

8:34 The formula didn't work with but it did with - Ty for the clear instructions. Great tutorial.

[@spreadsheetlife]

Thank you for your feedback! I'm glad you got it to work even though you had to tinker with it a bit. Enjoy using the calendar! 😊👍

[@noeymobile6625]

Hi, great tutorial, but i have a problem using in conditional formating , it's say invalid formula...would help me out with this ?

[@spreadsheetlife]

Try to make sure that your formula matches mine exactly. And if you are still having issues, let me know. Maybe reply to this comment with the formula you are using. That way I can better help!

[@nyaheadley]

This is amazing thank you. I'd like to create a calendar that I could write appointments in so i need space underneath the date. Normally i would put the date numbers on alternate rows ( leaving a row in between) How would i do that without messing up your formula? Also if i wanted to be able to choose between a sunday start and a monday start? Thank you so much in advance

[@spreadsheetlife]

Hi there! These are some excellent questions. Unfortunately, there isn't a simple answer. You would probably have to take a different approach to create the calendar you are describing. If you'd like, send me an email at [e-mail], and we can disuse a possible solution!

[@chine9407]

+1 on this question! Hope you make a tutorial on this one!

[@spreadsheetlife]

I will add it to the list! 👍

[@lindsaywilliams7865]

is there anyway to make it so your calendar dates are in a seperate cell and you have a row of cells under one week for writting things in that cordinate to that date?

[@spreadsheetlife]

Awesome question! This video goes over a different calendar that might be what you're looking for. kzbin.info/www/bejne/mKjGppqerrhohrMsi=sxXoP-dKvBoP-sM9

[@user-jc5ni3nf3m]

Is it possible to do this with empty rows in between each date? In other words, I'd like to have 1/2/24 in row 2, then 5 blank rows under it, then 1/9/24 in row 8 and so forth. The whole calendar would use 38 rows including the days of the week row.

[@spreadsheetlife]

Check out my other calendar video. It might be the solution you're looking for. 😊👍 kzbin.info/www/bejne/mKjGppqerrhohrMsi=_YTuSRDwj1oaTajJ

[@scroopmd6306]

thanks for the great tutorial. at 6:00 the datevalue function gives me an error when i enter can you please help me with that

[@spreadsheetlife]

I'm so glad you enjoyed the tutorial. As for the formula, can you tell me what you've entered so I can take a look at it?

[@mdavis3709]

WOW, this was such a great, well-delivered and user-friendly tutorial. Thanks so much. One question. Can this be made into an events calendar, where you can add links to event information, whether that info is housed inside of Google drive (i.e. Google Docs, Google Sheets, Google Forms, etc.) or external internet links? Looking to build something like this. Thanks again.🙂

[@spreadsheetlife]

Thank you so much for your kind words! It would be difficult to turn this specific calendar into an events calendar, but I can try and plan another tutorial in the future that is more similar to what you described. How does that sound?

[@mdavis3709]

@@spreadsheetlife Hey, YES! That would be awesome! Thanks so much. Looking forward to it.

[@spreadsheetlife]

I recently uploaded a new video on how to build a calendar where you can put in some information on each day. I hope this is what you were looking for. kzbin.info/www/bejne/mKjGppqerrhohrM

[@JamesEnTennessee]

Really nice and very useful. Can the calendar be hidden in a cell - apart from the data cells - and when you click on that cell the calendar will come up?

[@spreadsheetlife]

I'm so glad you found this video useful! And let me see if I understand your question. Are you asking if you can hide the entire calendar (which would be multiple cells)? And then unhide it at a specific time?

[@andreabolldorf675]

Thank you so much! Just what I was looking for to solve a problem. It worked great! I slightly changed the layout so that each month is in a row. What not worked in the contiditional format was to get the dates, which you greyed at the start and end of each month, to have no color. Do you happen to know if this is simply not possible or just a glitch on my system? Thanks again and keep up the fanstatic tutorials.

[@spreadsheetlife]

That's a good question. It's probably not a glitch, and I'm fairly certain it's possible to get it to work for your layout. The key is to get your conditional formatting formula just right. You are welcome to send me an email and I can take a look at it for you if you want!

[@andreabolldorf675]

@@spreadsheetlife thank you so much for your kind offer. I learnt the text or numbers in Google sheets needs to have a color. In conditional format I now gave them the color white, but I learnt also that the conditional format is not considered to be pushed through, when I download the Sheet into an XLX format. Is there a way to built in a formula that says, if you are not from this month, leave the cell empty? I am sure there is, but my brain is a bit foggy and thinking in circles ;)

[@spreadsheetlife]

Ah, I see! If you take the formula approach to leave cells empty, you will have to write a formula for each box in the calendar. So the SEQUENCE function approach won't be the best approach in that case.

[@andreabolldorf675]

@@spreadsheetlife Thanks much, I will get it to work somehow someday, and I love to work with formulas. :)

[@spreadsheetlife]

Okedokie! Sounds good. And of course, you can always email me if you have any questions!

[@ananyamishra2217]

Thanks a tonne!! Could you please tell what the yellow cell denotes?

[@spreadsheetlife]

You're very welcome! Yes, the yellow cell denotes the current day, and it updates automatically as each day goes by.

[@ananyamishra2217]

@@spreadsheetlife THANK YOU SO SO MUCH! May God bless you!

[@spreadsheetlife]

Happy to help! 😊

[@ConcettaHalstead]

This is great. I do have a question on my custom date formatting. I cleared the contents in the custom dates pulldown menu, but there is a "--" in the field that I cannot get rid of. Also the below format example shows "--5" as the day display option. All my calendar dates are showing the "--" before all the numbers. I've tripled checked the formulas I entered and it looks like everything matches what's in your video. Thoughts + Thank you!

[@spreadsheetlife]

Hmmm...I think I understand what you're saying, but it's a little difficult to diagnose the problem without more context. Could you share the file with me or send me screenshots at [e-mail]? It will be easier for me to help this way.

[@ConcettaHalstead]

@@spreadsheetlife -- I just shared my calendar with you via Google. Thank you for your time + insights. Appreciate it.

[@spreadsheetlife]

Okedokie! I went in and fixed it for you, and I also sent you an email! 😊👍

[@ConcettaHalstead]

THANK YOU!!!

[@spreadsheetlife]

You're very welcome! 😊

[@rahmatfirdaus3664]

Thanks.......................

[@spreadsheetlife]

You're welcome!

[@gauravaditi]

Hi there, thank you for sharing. This is helpful but I have one question: when I am changing the colour of any day in May it is automatically changing the fill in colour for September also. Please suggest a solution. I want to change the colour coding for every day (a unique colour) to refer to my completed tasks.

[@spreadsheetlife]

I think I understand the problem. If you manually add in the fill color, then that color will remain in the cells even if you change the month or year. So what you need to do is add conditional formatting that will apply a fill color to only those dates that match a list of dates that correspond with your completed tasks. Does that make sense?

[@gauravaditi]

@@spreadsheetlife ok can you share the steps what exactly I need to in conditional formatting?

[@spreadsheetlife]

Send me an email at [e-mail]. Let me know it's you and what we discussed here. It will be easier for me to help from there.

[@gauravaditi]

@@spreadsheetlife ok sure

[@spreadsheetlife]

😊👍

[@AaronAlabaIleleji]

Can I build this in the MS Excel App on my PC? And yes, it was very educative and fun.

[@spreadsheetlife]

I'm so glad that you found the video educative and fun! As for your question - yes, this can be built using MS Excel. You might have to do a few things a little differently. But it generally works the same way.

[@mamiiteyyy]

Hi the first time i did this the formula for date value worked but when i tried to redo it again because my original tracker got messed up date value is no longer working, is there anyway you can help me?

[@spreadsheetlife]

Absolutely! It will be easiest for me to help via email! Send me one, and I can help. 😊👍

[@paomoreno2527]

Idk why but the formula sends me to the day before of the actual date. I mean, January 1st 2024 is gonna be on Monday, but it keeps saying monday is the 31st. I don't understand!

[@spreadsheetlife]

Hmmm...you are welcome to email me and I can take a look at it for you.

[@pakemodin0228]

Can you make a video on dynamic calendar with list of holidays being highlighted?

[@spreadsheetlife]

That's a great suggestion. Since I've already made two calendar videos based in Google Sheets, the next one will probably be in Excel. However, if you want to see how to do it in Google Sheets, send me an email, and I can draw up a sample file for you. spreadsheetlife.com/contact/

[@LadyAlyzaCando]

Can I ask how to also highlight important dates like holidays

[@spreadsheetlife]

Yes. It's a little complicated, but take a look at this spreadsheet. Specifically, look at the conditional formatting rule I created. docs.google.com/spreadsheets/d/1_k191YpDZO_Xlpu6dp5KIJxyi8uHEEGbKgBeBb2Fs7Q/copy Hopefully this helps.

[@dariaparvulescu4969]

hello, your template is great, but when I try to enter the formula in H13, it gives me an error. it is from the DATEVALUE function. I didn't enter the parameters wrong, so that's why I'm confused

[@spreadsheetlife]

I see. Where are you located? Do you use a decimal for the thousand's separator in a number? If so, your formula syntax may be slightly different. spreadsheetlife.com/how-location-affects-formula-syntax-in-google-sheets/

[@pierasecchi8438]

Is it possible to start the calendar week with Monday instead of Sunday? Do I need to change the formula?

[@spreadsheetlife]

Yes. Change the formula at 6:47 to the following. =SEQUENCE(6, 7, H13-WEEKDAY(H13, 2)+1) Notice that a second argument has been added to the weekday function. Also, don't forget to re-label the days to MON through SUN in B4:H4.

[@pierasecchi8438]

​@@spreadsheetlifePerfect, it works perfectly, thank you so much

[@spreadsheetlife]

You are very welcome! I'm so glad it worked for you!

[@vivekkaushik9722]

It was very easy to use, although is there any video in which columns's length and width are huge and there is no date formula so that we can put all the information that we need to put for the entire month?

[@spreadsheetlife]

Check out this video. It might help. kzbin.info/www/bejne/mKjGppqerrhohrMsi=T9OaexrZzey_5ygC

[@vivekkaushik9722]

@@spreadsheetlife thanks for sharing it. I followed it and was able to create calendar. However, there is one problem. Say if choose a month Feb and write something for a date 20th Feb, then if choose month March, that text will remain there in the cell. This is a problem.

[@vivekkaushik9722]

In short I dont want the information to carry over if choose a different month

[@spreadsheetlife]

Gotcha. To fix this problem, make a copy of the worksheet for each month. Then simply rename each sheet tab with the month (Jan, Feb, Mar, etc.), and select the month and year for each worksheet. I hope this helps.

[@vivekkaushik9722]

@@spreadsheetlife it helps, but it is not possible to have all months data in same sheet?

[@JurMimay]

When i change the month, the dates are not changing. I dont know what i did wrong

[@spreadsheetlife]

I'm not sure. Could you tell me what your formula looks like?

[@soumiamoujane3695]

Does also work in Office 365?

[@spreadsheetlife]

Yes it does. Although the conditional formatting will be a little different.

[@soumiamoujane3695]

How different?

[@spreadsheetlife]

Well the formula and fundamentals of the functionality will be the same. The difference is the exact process for setting it up since the user interface is different for Excel and Google Sheets. Hopefully I am making sense. I'm sorry that it's difficult to explain the details in just a comment thread alone.

[@SrihariM-gt1py]

Links not working...

[@spreadsheetlife]

Thank you so much for letting me know. I really really appreciate it. I will adjust the links right away!

[@erinmcfall9634]

Formulas at 5:43

[@spreadsheetlife]

Hi there! Do you have a question about these formulas? Or are you just pointing out the timestamp of where they are?

[@hwangjjin]

Hi! Could you please help? I followed your instruction but when I entered the formula Datevalue at 5:43 the result was #Value. I dont know where I went wrong. @@spreadsheetlife

[@hwangjjin]

Hi! Could you please help? I followed exactly your intruction but when I entered the formula Datevalue at 5:43, the result was #Value. It said "DATEVALUE parameter 'JUNE 1 2023' cannot be parsed to date/time". I dont know where I went wrong.@@spreadsheetlife

[@hwangjjin]

​@@spreadsheetlifeHi! Could you please help? I followed exactly your instruction but when I entered the formula Datevalue at 5:43, the result was #Value. It said "DATEVALUE parameter 'JUNE 1 2023' cannot be parsed to date/time". I dont know where I went wrong.

[@spreadsheetlife]

No worries! It is usually easier for me to help via email. Send me an email and try to include a screenshot. I'll take a look at it for you. spreadsheetlife.com/contact/