How to Build a Dynamic Calendar in Google Sheets

Подписаться 6 тыс.

Просмотров 30 тыс.

50% 1

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

Опубликовано:

11 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 124

@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! 😊👍

@elmercartoneros560 2 месяца назад

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

@spreadsheetlife 2 месяца назад

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.

@ibrahimbhalfas8195 5 месяцев назад

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

@spreadsheetlife 5 месяцев назад

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.

@karinkott3885 22 дня назад

Crystal clear instructions. Thank you so much!!!

@spreadsheetlife 22 дня назад

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

@lenamccain4399 6 месяцев назад

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

@spreadsheetlife 6 месяцев назад

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

@lenamccain4399 6 месяцев назад

@@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 6 месяцев назад

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/

@user-zb8sf3dw3c 6 месяцев назад

IM SO HAPPY I FOUND THIS VIDEO

@spreadsheetlife 6 месяцев назад

I am too! Thank you for watching!! 😊

@ShyMartinez 3 месяца назад

Thanks immensely for this!!!!

@spreadsheetlife 3 месяца назад

You are so very welcome!! 😊👍

@branscsmsbc7522 11 месяцев назад

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 11 месяцев назад

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 7 месяцев назад

superb - beautifully created

@spreadsheetlife 7 месяцев назад

Thanks so much!

@nileshbaglekar7490 Месяц назад

Thanks a lot brother! Great work.

@spreadsheetlife Месяц назад

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

@user-ql7ng1xr3w 4 месяца назад

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

@spreadsheetlife 4 месяца назад

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

@gek-gek 5 месяцев назад

Thank u. I subscribe.❤

@spreadsheetlife 5 месяцев назад

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

@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 7 месяцев назад

@@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 6 месяцев назад

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!

@VanitysGunn 16 дней назад

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

@spreadsheetlife 16 дней назад

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! 😊👍

@rodrigocastilho9455 4 месяца назад

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 4 месяца назад

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. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-bwcughyS5PQ.html

@arandomuser188 6 месяцев назад

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 6 месяцев назад

Yes. It's usually easiest for me to help via email to diagnose specific problems. I encourage you to email me at info@spreadsheetlife.com, and I can help from there.

@mdavis3709 9 месяцев назад

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 9 месяцев назад

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 8 месяцев назад

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

@spreadsheetlife 6 месяцев назад

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. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-bwcughyS5PQ.html

@jasminedawkins875 8 месяцев назад

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 8 месяцев назад

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.

@rahmatfirdaus3664 Месяц назад

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

@spreadsheetlife Месяц назад

You're welcome!

@andreabolldorf675 7 месяцев назад

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 7 месяцев назад

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 7 месяцев назад

@@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 7 месяцев назад

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 7 месяцев назад

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

@spreadsheetlife 7 месяцев назад

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

@nyaheadley 11 месяцев назад

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 11 месяцев назад

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 info@spreadsheetlife.com, and we can disuse a possible solution!

@chine9407 10 месяцев назад

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

@spreadsheetlife 10 месяцев назад

I will add it to the list! 👍

@khairinakhairudin5275 7 месяцев назад

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 7 месяцев назад

Yes! Send me an email and I can help!

@cpiva794 12 дней назад

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

@spreadsheetlife 11 дней назад

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.

@JamesEnTennessee 4 месяца назад

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 4 месяца назад

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?

@lindsaywilliams7865 3 месяца назад

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 3 месяца назад

Awesome question! This video goes over a different calendar that might be what you're looking for. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-bwcughyS5PQ.htmlsi=sxXoP-dKvBoP-sM9

@AaronAlabaIleleji 9 месяцев назад

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

@spreadsheetlife 9 месяцев назад

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.

@noeymobile6625 9 месяцев назад

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

@spreadsheetlife 9 месяцев назад

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!

@ananyamishra2217 4 месяца назад

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

@spreadsheetlife 4 месяца назад

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

@ananyamishra2217 4 месяца назад

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

@spreadsheetlife 4 месяца назад

Happy to help! 😊

@user-jc5ni3nf3m 6 месяцев назад

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 6 месяцев назад

Check out my other calendar video. It might be the solution you're looking for. 😊👍 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-bwcughyS5PQ.htmlsi=_YTuSRDwj1oaTajJ

@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 info@spreadsheetlife.com? 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! 😊

@scroopmd6306 2 месяца назад

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 2 месяца назад

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?

@paomoreno2527 6 месяцев назад

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 6 месяцев назад

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

@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 info@spreadsheetlife.com. 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 Месяц назад

😊👍

@pakemodin0228 6 месяцев назад

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

@spreadsheetlife 6 месяцев назад

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/

@mamiiteyyy 4 месяца назад

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 4 месяца назад

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

@LadyAlyzaCando 3 месяца назад

Can I ask how to also highlight important dates like holidays

@spreadsheetlife 3 месяца назад

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.

@pierasecchi8438 2 месяца назад

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

@spreadsheetlife 2 месяца назад

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 2 месяца назад

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

@spreadsheetlife 2 месяца назад

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

@dariaparvulescu4969 2 месяца назад

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 2 месяца назад

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/

@vivekkaushik9722 4 месяца назад

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 4 месяца назад

Check out this video. It might help. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-bwcughyS5PQ.htmlsi=T9OaexrZzey_5ygC

@vivekkaushik9722 4 месяца назад

@@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 4 месяца назад

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

@spreadsheetlife 4 месяца назад

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 4 месяца назад

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

@soumiamoujane3695 6 месяцев назад

Does also work in Office 365?

@spreadsheetlife 6 месяцев назад

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

@soumiamoujane3695 4 месяца назад

How different?

@spreadsheetlife 4 месяца назад

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.

@JurMimay 2 месяца назад

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

@spreadsheetlife 2 месяца назад

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

@SrihariM-gt1py 3 месяца назад

Links not working...

@spreadsheetlife 3 месяца назад

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

@erinmcfall9634 6 месяцев назад

Formulas at 5:43

@spreadsheetlife 6 месяцев назад

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

@hwangjjin 3 месяца назад

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 3 месяца назад

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 3 месяца назад

@@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 3 месяца назад

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/