Followed step by step and working to set up my budget for next year - it was wonderful - so ready for the next one to learn how to do the frequency of events!
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
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
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.
This is awesome, thank you! One issue I've encountered: when adding an offset for using Monday as Day 1, this sometimes creates the bug that you lose Day 1 from the calendar altogether, and the month starts on Monday, Day 2. I think we saw this in your vid for Sep 2024, any ideas how to get round this?
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 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!
Great video! Im looking to do the opposite. I want to fill in information on a calendar and have that auto populate a list from the entries I put in the calendar. If this possible?
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)))
@@HashAliNZ Hi, thank you very much for this extremely detailed and helpful video. I've encountered a similar problem as they have, however I did a day for every cell instead of every third cell. Is there any ways to solve this problem in this case?
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?
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.
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!
Could you share the complete product as a template that users can access and copy on Google? That would be much easier for using it as a base to continue designing to our specs. Then we do not need to go through all the steps to get to the end product if that is what we are looking to create.
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)))
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.
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.
6.13 is there a formula where you can directly input data on the calendar and not create a separate table for the data for it to appear into the calendar?
@ i actually tried, i cant seem to find the correct formula for example i want to put salon appt at sept 1 when i click the october option on the dropdown the salon appt is still there
@@peachychaeee Yes, when you write something in the cells it will stay there until you delete it. You could use Apps Script to get it to delete when you switch to October, but it won't come back when you switch back to September. The list is the only way to keep track of things like that.
Hello, how are you? When I put the date in the date column of the details table, the item does not appear in my calendar using the formula, it only does so with the first cell and when I copy the format to the others, the item does not appear.
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
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...
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?
=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.
Followed step by step and working to set up my budget for next year - it was wonderful - so ready for the next one to learn how to do the frequency of events!
Amazing video from beginning to end. I am looking forward to the follow up video on this for recurring events. Thank you!
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
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
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.
This is a COMPLETE GUIDE! Thank you for your detailed help.
Thanks for the comment. The additional videos are coming soon - I promise!
This is awesome, thank you! One issue I've encountered: when adding an offset for using Monday as Day 1, this sometimes creates the bug that you lose Day 1 from the calendar altogether, and the month starts on Monday, Day 2. I think we saw this in your vid for Sep 2024, any ideas how to get round this?
this is really really impressive, amazing job
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.
at time 5:32 there is something wrong with the calendar. you loose the 1st in the september month. do you have a solution for this?
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!
Thank you Hash! Do you sell downloads of a calendar like this for those of us who are a not so great at this haha? Thank you!
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!
Great video! Im looking to do the opposite. I want to fill in information on a calendar and have that auto populate a list from the entries I put in the calendar. If this possible?
Interesting. I can do it from Google calendar to a spreadsheet but haven't explored doing it from a Google Sheet calendar like the this one to a list.
This is great! The one thing I was looking for, what if a bill is reoccurring instead of a one off? Is there a formula for that?
Absolutely. It's not straightforward, though. It's my next video, already recorded just gotta edit it. Will aim to get it out this week
bro at 1:35 sec how you converted that numbers into dates
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)))
@@HashAliNZ Hi, thank you very much for this extremely detailed and helpful video. I've encountered a similar problem as they have, however I did a day for every cell instead of every third cell. Is there any ways to solve this problem in this case?
Remove all the /3
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?
How would I use this calendar and/or table to pull through the current month's tasks/events to another worksheet and for it to be automated?
If you've already got the list in a other spreadsheet, check out the importrange function
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.
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! 😅💪
Could you share the complete product as a template that users can access and copy on Google? That would be much easier for using it as a base to continue designing to our specs. Then we do not need to go through all the steps to get to the end product if that is what we are looking to create.
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)))
Hi Laurine, in my comment click the See More button to reveal the rest of the formul
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.
AMAZING VIDEO, really need the frequency part, but thank you
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.
6.13 is there a formula where you can directly input data on the calendar and not create a separate table for the data for it to appear into the calendar?
You can do that with this setup. Just have the formulas for the dates and then use the rest of the space to put whatever you want
@ i actually tried, i cant seem to find the correct formula for example i want to put salon appt at sept 1 when i click the october option on the dropdown the salon appt is still there
@@peachychaeee Yes, when you write something in the cells it will stay there until you delete it. You could use Apps Script to get it to delete when you switch to October, but it won't come back when you switch back to September. The list is the only way to keep track of things like that.
Hello, how are you? When I put the date in the date column of the details table, the item does not appear in my calendar using the formula, it only does so with the first cell and when I copy the format to the others, the item does not appear.
=IFNA(FILTER($J$6:$J$23,$K$6:$K$23=B9)) i using this like you but nly works for me on the first cell, however when i copy only the format doesnt work
Thaaaaankyouuuuu this video helps alot!
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
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?
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...
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?
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 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
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!
Why wouln't you just share the link for this sheet so we could download it as a template?
Because it's incomplete. I will make it available eventually.
Hi. Please see my comment on your video pertaining to spreadsheet conditioning