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 can I make a section to type notes on each month in the black spaces at the beginning or end of calendar. I'm a home care nurse and I would like to customize each month to our use. Eg I would like to type " Date G tube changed ........ Date trach changed...... The nurse would just write in those dates. Can that be done?
That's a great question! So you can't add these notes to any section of the calendar where there are formula. But, you can indeed add notes to any of the cells surrounding the calendar. However, the notes won't update when you change the month. For what you are looking for, you might find this calendar a little more helpful. ruclips.net/video/bwcughyS5PQ/видео.htmlsi=wnIxmEdd_B9Ba5Ss
Thank you so much i was sitting for like 3 hours trying to figure it out, and your step by step done in under 10 minutes you are my hero, i was trying to add a live calendar to my todo list you are such a star, definitely worth a subscribe
That's so incredible to hear! 😄 Thank you so much for letting me know. It's so awesome to know that you were able to get through it and get everything working quickly and without struggle! 🎉
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.
The function for the conditional formatting with didn't work for me but this =MONTH(B3)Month(1&$B$1&$F$1) did for some reason lol With the formula in the video it kept highlighting all of the days or none of the days. In an earlier comment, it said this - worked but it did the same exact thing for me, highlighting everything. I know this video is a year old and there might have been an update with Google Sheets but that was the only thing and this video was extremely helpful and easily the most clear I've watched!
The conditional formatting formulas are definitely the most difficult to get working correctly, but I'm glad you were able to figure out a solution that works. Thanks for your kind words. I'm so glad you enjoyed the video! 😊
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.
@@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?
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?
Awesome question! This video goes over a different calendar that might be what you're looking for. ruclips.net/video/bwcughyS5PQ/видео.htmlsi=sxXoP-dKvBoP-sM9
I was just lead to this video after looking for a while. Where have you been? I've had much fun playing with, and putting this calendar together. so glad was finally found.
Thanks this is great! How do I add event to this calendar? Lets say if I select current month to display, and I want to add different events to the specific dates and make this plans for the whole year. Then by selecting month, I want to see only planned events and dates I have planned them for.
I'm so glad you enjoyed the video! Thank you for watching. 😊 So this calendar in particular cannot display or hold any events. However, I did make another set of two videos that show how to create a calendar that can contain event info. You can see part one here: ruclips.net/video/bwcughyS5PQ/видео.html
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.
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?
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.
So this calendar is only designed to display the date. For a calendar that allows for notes and events, I put together this video instead. ruclips.net/video/bwcughyS5PQ/видео.html
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!
Instructions were clear and concise! Quick question for you... if i wanted to use add info to the dates how would I go about doing that around the formulas? For instance, i want to add a dropdown with names of people im scheduling... how would I do that?
Thanks for the feedback! 😄 That's a great question. You might check out these videos below. ruclips.net/video/bwcughyS5PQ/видео.html ruclips.net/video/CBWy5MxbNO0/видео.html I hope these help.
I followed the steps up to 8:48 in the video. When I reached this point it showed all the dates as not part of the month. The formula (as this is part of something bigger I'm making on Google Sheets) I used was (as quoted), "=MONTH(M3)$C$56". Is this part of the code still valid or did this get updated? Otherwise I really love this video and was extremely easy to set up.
This formula is still valid. It's just tricky to figure out, especially if your setup is different. There are three main things you want to be sure are correct. 1- You've selected the entire calendar range to apply the conditional formatting. 2 - The cell reference within the MONTH function (in your case M3), is the reference to the top left cell of your calendar. 3 - The absolute reference (in your case $C$56) should reference the cell that contains the calculated Month number. Make sure all of these details are correct. If you are still having trouble, let me know.
@@spreadsheetlife Thank you for the information. I rechecked the sheet and did it again in a new sheet after trying to add this in another sheet. I was able to get this with the second try.
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.
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?
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.
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!
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.
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?
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?
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!
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. ruclips.net/video/bwcughyS5PQ/видео.html
Yes, I was looking for this kind of explanations. Thank you. Can you help with this: I have 13 sheets, one sheet is "Data", then 12 sheets are named after the 12 months. Some of these 12 sheets contains Tuition Fees and different bills to pay. I want to create a 14th sheet I will call "MasterSheet", where I want to gather the all sheet names not including the "Data" that contains the specific text say, "Tuition Fees". Please help.
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.
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
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!
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.
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.
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!
@@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 ;)
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.
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.
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?
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!
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
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/
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 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.
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.
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!
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!
@@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.
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!
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.
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.
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/
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.🙂
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?
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. ruclips.net/video/bwcughyS5PQ/видео.html
Thank you for asking about it. So the 6 and 7 tell the SEQUENCE function to create an array that spans 6 rows and 7 columns. If you have more questions let me know. 👍
@@spreadsheetlife you help me sooooooo much!!!! thank you!!! but, i still have a question, i hope you can help me again!!! how could i change the color only the last day of the month in this calendar? help me pleaseeeee😭😭😭😭😭😭😭😭
Hmmm...to do something like this, you'd need a very specific conditional formatting rule. This would be hard to explain in the comments alone. Send me an email at info@spreadsheetlife.com, and I can help more there. 👍
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
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
@@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.
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/
![I.N "HALLUCINATION" | [Stray Kids : SKZ-PLAYER]](http://i.ytimg.com/vi/n5B5q1Hwt_U/mqdefault.jpg)
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.
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! 😊👍
@@spreadsheetlife can I make a section to type notes on each month in the black spaces at the beginning or end of calendar. I'm a home care nurse and I would like to customize each month to our use. Eg I would like to type " Date G tube changed ........
Date trach changed......
The nurse would just write in those dates. Can that be done?
That's a great question! So you can't add these notes to any section of the calendar where there are formula. But, you can indeed add notes to any of the cells surrounding the calendar. However, the notes won't update when you change the month.
For what you are looking for, you might find this calendar a little more helpful.
ruclips.net/video/bwcughyS5PQ/видео.htmlsi=wnIxmEdd_B9Ba5Ss
Thank you so much i was sitting for like 3 hours trying to figure it out, and your step by step done in under 10 minutes you are my hero, i was trying to add a live calendar to my todo list you are such a star, definitely worth a subscribe
That's so incredible to hear! 😄
Thank you so much for letting me know. It's so awesome to know that you were able to get through it and get everything working quickly and without struggle! 🎉
Fantastic instructions! Clear and concise. Thank you!
Thank you for watching! 😊
Crystal clear instructions. Thank you so much!!!
You are so welcome! I hope you enjoy the calendar, and thank you so much for your feedback. 😄
SOOOOOOOO HELPFUL! Thanks a Mil for providing this content with such easy to follow instructions.
Thank you so much for your kind words, and thank you for watching!! 😊
Wow🎉 Thank you for being an awesome teacher. This was exciting and i learnt so much from your video. Thank you.
Thank you for your kind words. You're most welcome! I'm so glad you enjoyed the video. 😊
Your directions is so amazing very clear and definitely very helpful. Jazakallah khair brother!
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.
The function for the conditional formatting with didn't work for me but this =MONTH(B3)Month(1&$B$1&$F$1) did for some reason lol With the formula in the video it kept highlighting all of the days or none of the days. In an earlier comment, it said this - worked but it did the same exact thing for me, highlighting everything. I know this video is a year old and there might have been an update with Google Sheets but that was the only thing and this video was extremely helpful and easily the most clear I've watched!
The conditional formatting formulas are definitely the most difficult to get working correctly, but I'm glad you were able to figure out a solution that works. Thanks for your kind words. I'm so glad you enjoyed the video! 😊
You are amazing! Easy and understandable. All your videos are informative and applicable to me. Million thanks 🙏🏻
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.
Thank you so much for this! I've been trying to figure this out for what seems like forever. Really appreciate it!
Thanks so much for sharing! I'm so glad that you found this video and were able to benefit from it.
@@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?
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/
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?
Awesome question! This video goes over a different calendar that might be what you're looking for.
ruclips.net/video/bwcughyS5PQ/видео.htmlsi=sxXoP-dKvBoP-sM9
I was just lead to this video after looking for a while. Where have you been? I've had much fun playing with, and putting this calendar together. so glad was finally found.
I'm so glad that you discovered my channel! 😊
It's amazing to hear that you've enjoyed the tutorial so much!
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.
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!!
This was really helpful and fun! Reminds me of making a Myspace page hahah Thank you so much!!
Lol, that's awesome! I'm so glad that you had such a good time with it! 😄
IM SO HAPPY I FOUND THIS VIDEO
I am too! Thank you for watching!! 😊
Thanks this is great! How do I add event to this calendar? Lets say if I select current month to display, and I want to add different events to the specific dates and make this plans for the whole year. Then by selecting month, I want to see only planned events and dates I have planned them for.
I'm so glad you enjoyed the video! Thank you for watching. 😊
So this calendar in particular cannot display or hold any events. However, I did make another set of two videos that show how to create a calendar that can contain event info. You can see part one here: ruclips.net/video/bwcughyS5PQ/видео.html
Thank you so much, best of luck of your RUclips channel
Of course!! Thank you for watching, and thank you so much for your support! 😊
amazing! thank you very very very much for your amazing video
You are very welcome! I am so glad you enjoyed the video! 😄
superb - beautifully created
Thanks so much!
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.
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.
Thanks a lot brother! Great work.
You're very welcome! Thanks so much! 😊👍
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
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?
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?
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.
How do I add notes or mark specific dates within the year. When I add a note it is added in all the cells in every month.
So this calendar is only designed to display the date. For a calendar that allows for notes and events, I put together this video instead.
ruclips.net/video/bwcughyS5PQ/видео.html
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!
Yes! Send me an email and I can help!
great! now i can make my own calendar
Yay!!!! 😄🎉
Thanks immensely for this!!!!
You are so very welcome!! 😊👍
Instructions were clear and concise! Quick question for you... if i wanted to use add info to the dates how would I go about doing that around the formulas? For instance, i want to add a dropdown with names of people im scheduling... how would I do that?
Thanks for the feedback! 😄
That's a great question. You might check out these videos below.
ruclips.net/video/bwcughyS5PQ/видео.html
ruclips.net/video/CBWy5MxbNO0/видео.html
I hope these help.
I followed the steps up to 8:48 in the video. When I reached this point it showed all the dates as not part of the month. The formula (as this is part of something bigger I'm making on Google Sheets) I used was (as quoted), "=MONTH(M3)$C$56". Is this part of the code still valid or did this get updated? Otherwise I really love this video and was extremely easy to set up.
This formula is still valid. It's just tricky to figure out, especially if your setup is different. There are three main things you want to be sure are correct.
1- You've selected the entire calendar range to apply the conditional formatting.
2 - The cell reference within the MONTH function (in your case M3), is the reference to the top left cell of your calendar.
3 - The absolute reference (in your case $C$56) should reference the cell that contains the calculated Month number.
Make sure all of these details are correct. If you are still having trouble, let me know.
@@spreadsheetlife Thank you for the information. I rechecked the sheet and did it again in a new sheet after trying to add this in another sheet. I was able to get this with the second try.
Oh that's awesome! I'm glad that you were able to get it to work! 😄
Thanks a tonne!! Could you please tell what the yellow cell denotes?
You're very welcome! Yes, the yellow cell denotes the current day, and it updates automatically as each day goes by.
@@spreadsheetlife THANK YOU SO SO MUCH! May God bless you!
Happy to help! 😊
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.
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?
@@spreadsheetlife ok can you share the steps what exactly I need to in conditional formatting?
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.
@@spreadsheetlife ok sure
😊👍
Hi! Thank you for the video!!
One question, how can I add the number of week to the right?
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.
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!
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.
@@spreadsheetlife -- I just shared my calendar with you via Google. Thank you for your time + insights. Appreciate it.
Okedokie! I went in and fixed it for you, and I also sent you an email! 😊👍
THANK YOU!!!
You're very welcome! 😊
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?
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?
8:34 The formula didn't work with but it did with -
Ty for the clear instructions. Great tutorial.
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! 😊👍
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!
Hmmm...you are welcome to email me and I can take a look at it for you.
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
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. ruclips.net/video/bwcughyS5PQ/видео.html
Hi, what if I want my calendar to start on a Monday?
Change the formula in cell B6 to the following:
=SEQUENCE(6,7,H13-WEEKDAY(H13,2)+1)
I hope this helps.
Yes, I was looking for this kind of explanations. Thank you.
Can you help with this:
I have 13 sheets, one sheet is "Data", then 12 sheets are named after the 12 months.
Some of these 12 sheets contains Tuition Fees and different bills to pay.
I want to create a 14th sheet I will call "MasterSheet", where I want to gather the all sheet names not including the "Data" that contains the specific text say, "Tuition Fees".
Please help.
This sounds like a question best answered via email. Send me a message at info@spreadsheetlife.com, and we can go from there!
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.
Check out my other calendar video. It might be the solution you're looking for. 😊👍
ruclips.net/video/bwcughyS5PQ/видео.htmlsi=_YTuSRDwj1oaTajJ
How do we add texts in these each cube tho
For a calendar with text input space, I recommend you checkout this video: ruclips.net/video/bwcughyS5PQ/видео.html
@spreadsheetlife awesome! Thanks alot ✨
Of course! Happy to help! 😊
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
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!
+1 on this question! Hope you make a tutorial on this one!
I will add it to the list! 👍
BRILLIANT
😄😊👍
Is it possible to start the calendar week with Monday instead of Sunday? Do I need to change the formula?
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.
@@spreadsheetlifePerfect, it works perfectly, thank you so much
You are very welcome! I'm so glad it worked for you!
Thank u. I subscribe.❤
Thank you so much for subscribing! I'm glad you enjoyed the video.
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.
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!
@@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 ;)
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.
@@spreadsheetlife Thanks much, I will get it to work somehow someday, and I love to work with formulas. :)
Okedokie! Sounds good. And of course, you can always email me if you have any questions!
Can I ask how to also highlight important dates like holidays
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.
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?
Absolutely! It will be easiest for me to help via email! Send me one, and I can help. 😊👍
Hi, great tutorial, but i have a problem using in conditional formating , it's say invalid formula...would help me out with this ?
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!
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
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/
Hi! how can you change the colors of the month? example for Jan it's pink then next month it's blue.
btw your tutorial is amazing, helped me a lot!
I'm so glad that you enjoyed the tutorial. If you send me an email at info@spreadsheetlife.com, I can briefly explain how to do this.
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?
Check out this video. It might help. ruclips.net/video/bwcughyS5PQ/видео.htmlsi=T9OaexrZzey_5ygC
@@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.
In short I dont want the information to carry over if choose a different month
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.
@@spreadsheetlife it helps, but it is not possible to have all months data in same sheet?
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!
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!
@@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.
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!
When i change the month, the dates are not changing. I dont know what i did wrong
I'm not sure. Could you tell me what your formula looks like?
Does also work in Office 365?
Yes it does. Although the conditional formatting will be a little different.
How different?
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.
Hi, how do you change the colors of each month?
btw, your tutorial is amazing!
I responded to your other comment. 😊👍
Can I build this in the MS Excel App on my PC?
And yes, it was very educative and fun.
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.
Can you make a video on dynamic calendar with list of holidays being highlighted?
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/
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.🙂
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?
@@spreadsheetlife Hey, YES! That would be awesome! Thanks so much. Looking forward to it.
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.
ruclips.net/video/bwcughyS5PQ/видео.html
Links not working...
Thank you so much for letting me know. I really really appreciate it. I will adjust the links right away!
Hi, sorry, but I don't get your point when you start entering the formula =sequence (6,7......). What is 6, 7? It's not fully mentioned in your step.
Thank you for asking about it. So the 6 and 7 tell the SEQUENCE function to create an array that spans 6 rows and 7 columns. If you have more questions let me know. 👍
@spreadsheetlife 6:00 why did I enter the formula like in the video but it got an error saying "VALUE"
Hmmm...have you both selected a month and entered a value for the year? If you have not done those things, it will give you an error.
Why the 14 though? 8:37
So the 14 is part of that reference. You are referencing cell H14. 😊👍
gloria gringo me ajudou muito
🤙🤙🤙🤙🤙🤙🤙🤙🤙🤙👍👍👍👍👍👍👍👍👍👍👍
I'm glad you found this video helpful! 😊👍
@@spreadsheetlife you help me sooooooo much!!!! thank you!!! but, i still have a question, i hope you can help me again!!!
how could i change the color only the last day of the month in this calendar? help me pleaseeeee😭😭😭😭😭😭😭😭
Hmmm...to do something like this, you'd need a very specific conditional formatting rule. This would be hard to explain in the comments alone. Send me an email at info@spreadsheetlife.com, and I can help more there. 👍
Formulas at 5:43
Hi there! Do you have a question about these formulas? Or are you just pointing out the timestamp of where they are?
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
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
@@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.
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/
Thanks.......................
You're welcome!