THIS VIDEO IS A GAME CHANGER!!!!!! I WAS LOOKING TO DO THIS BUT DIDN'T KNOW HOW TO. NOW I DO!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! THANK YOU. It was a bit fast for a beginner like me but I re-watched to figure it out. But it would be great if it only displays the exact days of the month.
Absolutely love this! Would love to see an integration from Google Calendar (without having to pay for a premium product) and reoccurring events too :) Thank you
Thank you so much for this. You are a huge help! Explaining the errors in the formula and adding little tweaks to make it look better too. Quick question: I've been trying to modify my calendar using checkboxes for finished tasks and using conditional formatting that greys out the task in the calendar but I can't seem to make it work. Hoping you could help!
Another great video. I did have some questions. 1)with this calendar if you go to feb =#Value I am wondering how fix this for when feb 25 arrives? 2)I feel this is going to be a duh question, but after added in columns Conditional format for greying out previous month and next month I am having difficulty with. I feel I am just missing a simple step that is completely illuding me. I am definitely looking forward to more info on the recurring events.
What an amazing video and tutorial! Its really easy to follow up. Wanted to ask: what if i would like for the calendar to highlight vacations dates on the calendar? Ex: If i add a "from" date and a "to" date, the days in the middle to be highlited? Probably this will be the next tutorial and talking about "frequency" but im a little stuck on this. Hope you could help me.
The next one about frequency will be recurring items. Like something that comes about weekly or monthly. For holidays that are just single days, that can just be added to the list of recurring events. For multi-day vacations with a from and to would be much more difficult. I'll see what I can do, but no promises and it won't be in the next two videos.
I wasn't going to release this one publically - there's still two more videos to go for it. I've recorded one of the videos and still editing it. The other is still in the works. But for you, Robert, I can absolutely give you the template. Send me an email and I'll send it off to you: themathlabnz@gmail.com
Thank you so much for this! I'm having just a small issue after adding the new columns. I can't get the formatted condition to work properly (regarding the light grey for the other months). I have 2 columns and 5 lines for each day and the formatted condition for the day itself works fine, but when I try to bring it for the lines below, it doesn't work properly on them. Any tips?
Hi! Love your content , one question, will the calendar change accordingly while transitioning to another month or do we need to do that manually? (changing the month manually from October to November) If no, how do we do that automatically :)
In this video, I've made it so you need to manually select the month. This takes like two clicks. However, in the follow-up video for recurring items (coming soon - I've just had a bit of a setback with it!), I'll include a segment showing how to do it automatically.
If using Monday as the start day of the week, how do I still display the first of the month (on the Sunday)? As per your video at ~5min18sec - the first day displayed in your calendar is Monday 02/09 but I want to be able to include a row above only display Sunday 01/09 at the end of the table. Hope that makes sense..😊
Sorry I missed your comment, Katie. The formula gets quite complex when adding in that fix. Here it is: =if(and(day(floor(date($C$3,month($C$2&1),1)-(weekday(date($C$3,month($C$2&1),1))-if($H$2="Monday",1,0)-column($E$3)/3)))>1, day(floor(date($C$3,month($C$2&1),1)-(weekday(date($C$3,month($C$2&1),1))-if($H$2="Monday",1,0)-column($E$3)/3)))
@christajohnson1679 Hey Christa, The formula gets quite complex when adding in that fix. =if(and(day(floor(date($C$3,month($C$2&1),1)-(weekday(date($C$3,month($C$2&1),1))-if($H$2="Monday",1,0)-column($E$3)/3)))>1, day(floor(date($C$3,month($C$2&1),1)-(weekday(date($C$3,month($C$2&1),1))-if($H$2="Monday",1,0)-column($E$3)/3)))
Hi Hash, this is so helpful! Thanks so much! I just got a question, with this "date($C$2,month($C$3&1),1)-( weekday(date($C$2,month($C$3&1),1))-if($B$7="Monday",1,0)-column(A3))", the tables changes according to the Monday/Sunday chosen, but for example, Dec 2024, chose "Monday", the starting date of the month table will be "2/12/2024" instead of "1/12/2024" which only works when click 'Sun'. (and it's like that for the month of Jan 2023). Is there a way I can make the table always show the full dates of the month whether start date of the week be "Mon" or "Sun"? Very appreciated!!
Hey Ian, The formula gets quite complex when adding in that fix. =if(and(day(floor(date($C$3,month($C$2&1),1)-(weekday(date($C$3,month($C$2&1),1))-if($H$2="Monday",1,0)-column($E$3)/3)))>1, day(floor(date($C$3,month($C$2&1),1)-(weekday(date($C$3,month($C$2&1),1))-if($H$2="Monday",1,0)-column($E$3)/3)))
Absolutely. I've been very swamped so that one isn't recorded yet. The other video, integrating with Google calendar, is recorded and I'm still editing it.
Aw dang it is there anyway you could type out how to do the frequency if it’s quick? I would greatly appreciate it. It’s the last part that I need for my business this video was amazing
hi there, this has been very helpful. but ive got issues when i wanted to do conditional formatting to grey out the dates. ive tried =day(b6)>7 but somehow there are couples of cells are being highlighted as well. could u please help? thanks! and im curious as to why you changed the A3 to E3? what if i only add 1 more columns? should i change the A3 to B3 then divide it by 2 instead?
Hey Regina, It's difficult to tell why the day(B6)>7 would be greyed out for cells that are less than 7. I'd need to dive it to find out.. Watching the video back, it probably would've made more sense to change A3 to C3 because I put in 3 columns between each day of the week. I wouldn't have needed the FLOOR function if I used C3 instead.. Yes, if you add one more column, then change A3 to B3 and divide by 2. You got it!
Hello! When I apply the formula to my calendar, I get a #calc! rather than a #na with it saying empty array/empty arrays not supported. I'm stumped on how to clear those cells similar to how you would clear #na. The dates that need to be filled work correctly and I'm pulling data from a separate sheet on the same excel file using a different calendar layout which spreads the months over 12 different sheets if that effects anything
@HashAliNZ Ah I see, I'm using excel didn't even notice you were on google sheets. Have to use excel to add what I'm trying to schedule into a Microsoft teams tab unfortunately.
Does anyone know how I can add the frequency input to the input that is connected to the item and date tabs already? Is there just a simple input I can copy and paste?
Thank you for this tutorial, however, the formula for the dates do not work for me. (=DATE$C$3,MONTH($C$2&1),1)-(WEEKDAY(DATE($C$3),MONTH($C$2&1),1)-IF($C$5="Monday",1,0)-COLUMNA3)) I set it to Monday and using everything exactly as you did, the first date rurns to Mon, 26/09??.🥺 Please advise. I only want a formula , so that i can choose between Monday and Sunday, the dates are correctand then I'll see to it that the dates only start showing for the current month. 😀🥰
Looks like you've got a bracket in the wrong place. Inside your weekday function you've got DATE($A$3) Dont close the bracket there. Also, you need a bracket for the COLUMN function at the end of your formula
@@HashAliNZThank you for your quick reply.💜 Iindeed, I should have copy pasted what I have, now I pasted exactly from what I entered for the first day😀 =DATE(C3,MONTH(C2&1),1)-(WEEKDAY(DATE(C3,MONTH(C2&1),1)-IF(C5="Monday",1,0)-COLUMN(A3))) And the date that shows up is: "26", so, 26/08/2024...
=if(E2="Monday",("Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"),("Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")) I tried this formula and after deleting the cell from Tuesday to Saturday it still blank.
Sorry, life has gotten in the way. I'll get it done when I can. Unfortunately it's not as simple as just giving a formula. Have to add a bit more structure around it all
I have been trying to get the sheet to export the tasks to the calendar but it's not working for me. Do you have any ideas? I am pretty sure I am using the formula correctly but its not doing anything
Creating calendars from a list of details like in this video is quite a complex thing to do - especially if you've got extra rows and columns per day. A single bracket out of place can mess the entire thing up.
Amazing video from beginning to end. I am looking forward to the follow up video on this for recurring events. Thank you!
This is a COMPLETE GUIDE! Thank you for your detailed help.
Thanks for the comment. The additional videos are coming soon - I promise!
THIS VIDEO IS A GAME CHANGER!!!!!! I WAS LOOKING TO DO THIS BUT DIDN'T KNOW HOW TO. NOW I DO!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! THANK YOU. It was a bit fast for a beginner like me but I re-watched to figure it out. But it would be great if it only displays the exact days of the month.
Absolutely love this! Would love to see an integration from Google Calendar (without having to pay for a premium product) and reoccurring events too :) Thank you
Thank you so much for this. You are a huge help! Explaining the errors in the formula and adding little tweaks to make it look better too. Quick question: I've been trying to modify my calendar using checkboxes for finished tasks and using conditional formatting that greys out the task in the calendar but I can't seem to make it work. Hoping you could help!
Another great video. I did have some questions. 1)with this calendar if you go to feb =#Value I am wondering how fix this for when feb 25 arrives? 2)I feel this is going to be a duh question, but after added in columns Conditional format for greying out previous month and next month I am having difficulty with. I feel I am just missing a simple step that is completely illuding me. I am definitely looking forward to more info on the recurring events.
Best tutorial on calendars and organization!!
I would really appreciate the next steps where you can have monthly and weekly tasks added automatically please!!!
@meditationrocks2417 absolutely. I'm working on them, life just keeps interrupting. I'll get them done as soon as possible!
What an amazing video and tutorial! Its really easy to follow up.
Wanted to ask: what if i would like for the calendar to highlight vacations dates on the calendar? Ex: If i add a "from" date and a "to" date, the days in the middle to be highlited? Probably this will be the next tutorial and talking about "frequency" but im a little stuck on this. Hope you could help me.
The next one about frequency will be recurring items. Like something that comes about weekly or monthly. For holidays that are just single days, that can just be added to the list of recurring events. For multi-day vacations with a from and to would be much more difficult. I'll see what I can do, but no promises and it won't be in the next two videos.
Thank you Hash. Excellent hands-on end-to-end actual. Any chance we can acquire a template of this so we can extend scope?
I wasn't going to release this one publically - there's still two more videos to go for it. I've recorded one of the videos and still editing it. The other is still in the works.
But for you, Robert, I can absolutely give you the template. Send me an email and I'll send it off to you: themathlabnz@gmail.com
Thank you so much for this!
I'm having just a small issue after adding the new columns. I can't get the formatted condition to work properly (regarding the light grey for the other months).
I have 2 columns and 5 lines for each day and the formatted condition for the day itself works fine, but when I try to bring it for the lines below, it doesn't work properly on them. Any tips?
Thaaaaankyouuuuu this video helps alot!
Hi! Love your content , one question, will the calendar change accordingly while transitioning to another month or do we need to do that manually? (changing the month manually from October to November)
If no, how do we do that automatically :)
In this video, I've made it so you need to manually select the month. This takes like two clicks. However, in the follow-up video for recurring items (coming soon - I've just had a bit of a setback with it!), I'll include a segment showing how to do it automatically.
If using Monday as the start day of the week, how do I still display the first of the month (on the Sunday)? As per your video at ~5min18sec - the first day displayed in your calendar is Monday 02/09 but I want to be able to include a row above only display Sunday 01/09 at the end of the table.
Hope that makes sense..😊
I'm having the same issue trying to figure that out. Did you get a solution?
Sorry I missed your comment, Katie. The formula gets quite complex when adding in that fix. Here it is:
=if(and(day(floor(date($C$3,month($C$2&1),1)-(weekday(date($C$3,month($C$2&1),1))-if($H$2="Monday",1,0)-column($E$3)/3)))>1,
day(floor(date($C$3,month($C$2&1),1)-(weekday(date($C$3,month($C$2&1),1))-if($H$2="Monday",1,0)-column($E$3)/3)))
@christajohnson1679 Hey Christa, The formula gets quite complex when adding in that fix.
=if(and(day(floor(date($C$3,month($C$2&1),1)-(weekday(date($C$3,month($C$2&1),1))-if($H$2="Monday",1,0)-column($E$3)/3)))>1,
day(floor(date($C$3,month($C$2&1),1)-(weekday(date($C$3,month($C$2&1),1))-if($H$2="Monday",1,0)-column($E$3)/3)))
Hi Hash, this is so helpful! Thanks so much! I just got a question, with this "date($C$2,month($C$3&1),1)-( weekday(date($C$2,month($C$3&1),1))-if($B$7="Monday",1,0)-column(A3))", the tables changes according to the Monday/Sunday chosen, but for example, Dec 2024, chose "Monday", the starting date of the month table will be "2/12/2024" instead of "1/12/2024" which only works when click 'Sun'. (and it's like that for the month of Jan 2023). Is there a way I can make the table always show the full dates of the month whether start date of the week be "Mon" or "Sun"? Very appreciated!!
Hey Ian, The formula gets quite complex when adding in that fix.
=if(and(day(floor(date($C$3,month($C$2&1),1)-(weekday(date($C$3,month($C$2&1),1))-if($H$2="Monday",1,0)-column($E$3)/3)))>1,
day(floor(date($C$3,month($C$2&1),1)-(weekday(date($C$3,month($C$2&1),1))-if($H$2="Monday",1,0)-column($E$3)/3)))
Hello everything went amazing thank you so much. Can you pls show how to do frequency that is all I need 😅
Absolutely. I've been very swamped so that one isn't recorded yet. The other video, integrating with Google calendar, is recorded and I'm still editing it.
Aw dang it is there anyway you could type out how to do the frequency if it’s quick? I would greatly appreciate it. It’s the last part that I need for my business this video was amazing
hi there, this has been very helpful. but ive got issues when i wanted to do conditional formatting to grey out the dates. ive tried =day(b6)>7 but somehow there are couples of cells are being highlighted as well. could u please help? thanks!
and im curious as to why you changed the A3 to E3? what if i only add 1 more columns? should i change the A3 to B3 then divide it by 2 instead?
Hey Regina,
It's difficult to tell why the day(B6)>7 would be greyed out for cells that are less than 7. I'd need to dive it to find out..
Watching the video back, it probably would've made more sense to change A3 to C3 because I put in 3 columns between each day of the week. I wouldn't have needed the FLOOR function if I used C3 instead.. Yes, if you add one more column, then change A3 to B3 and divide by 2. You got it!
@@HashAliNZ thanks Hash! this automation helps me a lot to track from other data.
keep on making this kind of videos!
@reginarianti91 absolutely - just gotta wait for a break from regular life to be able to get back to it! 😅💪
Hello! When I apply the formula to my calendar, I get a #calc! rather than a #na with it saying empty array/empty arrays not supported. I'm stumped on how to clear those cells similar to how you would clear #na. The dates that need to be filled work correctly and I'm pulling data from a separate sheet on the same excel file using a different calendar layout which spreads the months over 12 different sheets if that effects anything
Are you using Excel or Google Sheets? This tutorial, and all videos on my channel, are in Google Sheets
@HashAliNZ Ah I see, I'm using excel didn't even notice you were on google sheets. Have to use excel to add what I'm trying to schedule into a Microsoft teams tab unfortunately.
Great 👍🏼 Is it to pay you to do some modifications so it matches my needs?!
Absolutely. Get in touch with me at themathlabnz@gmail.com
Yes absolutely. Just get in touch through themathlabnz@gmail.com
Could you share this calendar template to help us get started?
Does anyone know how I can add the frequency input to the input that is connected to the item and date tabs already? Is there just a simple input I can copy and paste?
Thank you for this tutorial, however, the formula for the dates do not work for me.
(=DATE$C$3,MONTH($C$2&1),1)-(WEEKDAY(DATE($C$3),MONTH($C$2&1),1)-IF($C$5="Monday",1,0)-COLUMNA3))
I set it to Monday and using everything exactly as you did, the first date rurns to Mon, 26/09??.🥺 Please advise. I only want a formula , so that i can choose between Monday and Sunday, the dates are correctand then I'll see to it that the dates only start showing
for the current month. 😀🥰
Looks like you've got a bracket in the wrong place. Inside your weekday function you've got DATE($A$3)
Dont close the bracket there.
Also, you need a bracket for the COLUMN function at the end of your formula
@@HashAliNZThank you for your quick reply.💜
Iindeed, I should have copy pasted what I have, now I pasted exactly from what I entered for the first day😀
=DATE(C3,MONTH(C2&1),1)-(WEEKDAY(DATE(C3,MONTH(C2&1),1)-IF(C5="Monday",1,0)-COLUMN(A3)))
And the date that shows up is: "26", so, 26/08/2024...
I don't get the formula after the REF appeared, I tried to delete from Tuesday to Saturday but its still REF.
=if(E2="Monday",("Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"),("Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")) I tried this formula and after deleting the cell from Tuesday to Saturday it still blank.
when using sequence function, it appeared 45536, how you convert into 1. Because you have not explained it yet in your video
Check out the timestamp from 0:08 to 0:14
Select them, format as d (for day)
@@HashAliNZ Thank you. When will you upload the next video for accommodating frequency in the dynamic calendar?
Sorry, life has gotten in the way. I'll get it done when I can. Unfortunately it's not as simple as just giving a formula. Have to add a bit more structure around it all
At time 1.34, how did the 4 digit numbers change to the actual dates? Pls help and thank you!
Select those cells with the 5 digit numbers in them then go up to Format > Number > Custom Number Format and write in the box d
I have been trying to get the sheet to export the tasks to the calendar but it's not working for me. Do you have any ideas? I am pretty sure I am using the formula correctly but its not doing anything
Creating calendars from a list of details like in this video is quite a complex thing to do - especially if you've got extra rows and columns per day. A single bracket out of place can mess the entire thing up.
@@HashAliNZ BTW I managed! All I needed was a bit of sleep and come again fresh...
Amazing! I've got the next two calendar videos coming soon, sorry for making you wait!
Hi. Please see my comment on your video pertaining to spreadsheet conditioning