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.
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.
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.
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.
@@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?
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. th-cam.com/video/bwcughyS5PQ/w-d-xo.html th-cam.com/video/CBWy5MxbNO0/w-d-xo.html I hope these help.
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!
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.
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!
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!
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. th-cam.com/video/bwcughyS5PQ/w-d-xo.htmlsi=sxXoP-dKvBoP-sM9
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'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. th-cam.com/video/bwcughyS5PQ/w-d-xo.html
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.
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.
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?
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?
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.
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.
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. th-cam.com/video/bwcughyS5PQ/w-d-xo.html
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/
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.
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!
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.
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.
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.
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.
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!
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?
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/
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.
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! 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/
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! 😊👍
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!
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.
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.
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. 😄
IM SO HAPPY I FOUND THIS VIDEO
I am too! Thank you for watching!! 😊
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/
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.
th-cam.com/video/bwcughyS5PQ/w-d-xo.html
th-cam.com/video/CBWy5MxbNO0/w-d-xo.html
I hope these help.
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!
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! 😊👍
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.
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 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!
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.
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.
th-cam.com/video/bwcughyS5PQ/w-d-xo.htmlsi=sxXoP-dKvBoP-sM9
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 immensely for this!!!!
You are so very welcome!! 😊👍
Thanks a lot brother! Great work.
You're very welcome! Thanks so much! 😊👍
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. th-cam.com/video/bwcughyS5PQ/w-d-xo.html
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.
amazing! thank you very very very much for your amazing video
You are very welcome! I am so glad you enjoyed the video! 😄
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!
superb - beautifully created
Thanks so much!
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
😊👍
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?
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! 😄
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!
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.
th-cam.com/video/bwcughyS5PQ/w-d-xo.html
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/
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! 😊
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! 👍
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. th-cam.com/video/bwcughyS5PQ/w-d-xo.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?
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.
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!
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. 😊👍
th-cam.com/video/bwcughyS5PQ/w-d-xo.htmlsi=_YTuSRDwj1oaTajJ
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?
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!
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. 😊👍
Thank u. I subscribe.❤
Thank you so much for subscribing! I'm glad you enjoyed the video.
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! 😊
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/
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?
Thanks.......................
You're welcome!
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.
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.
Links not working...
Thank you so much for letting me know. I really really appreciate it. I will adjust the links right away!
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/