You can always download it for absolutely free we have through Facebook or through email just click on the links in the description and it's absolutely yours in this week what we're gonna do is we're gonna create a really dynamic yearly activity scheduler and what I want to do is I want to create a calendar base have all 12 months visible on the screen along with the daily activities so the idea is for us to be able to click on any specific date have those activities perhaps and display here enter any of those activities in a column and then have those activities.
Automatically saved so when we click on that date again they're all there I'm putting in some conditional formatting we want to know the current day we want to know the selected day and I also want to have a dynamic starting day maybe you want your day starting on Monday or Sunday or whatever day we're gonna have that and of course changing for a year so that's what we're gonna be covering and I can't wait to show it to you schedules can be really incredibly powerful especially in Excel and I can't wait to show you how to do that before we get started I just want to let you know it is a difficult time out there I.
Do understand that n is no better time than to learn your skills to up those skills so you can work from home or from wherever you are in the world to help you do that I've created these training videos each and every week I also have a mentorship program that's gonna help you define design develop and deploy your own applications so if this is something you like you don't have to get fired from any job or let go from any job ever again once you increase your skills learn how to make and sell your own applications and work from home so I.
Hope this and this training can help you the mentorship will take you that extra step and get you all completely financially independent so I really hope to help you also just in case if you like these applications I've got over 100 of them for just $37 I'm gonna include the link down below that really helps us out that keeps these training videos free each and every week so thanks alright well let's get started on this week's training so basically the only thing I've done this is kind of sighs some columns to help us.
Out a little move things a little bit quicker other than that everything we're gonna be doing during this live training so let's get started what I want to do is I'm gonna highlight all of the columns here and then I want to just give it a nice look just as we always do in this one in this one we're not gonna hide columns and B as we have in the past so just so we know there's really not going to be any hit and this one's gonna be extremely light on VBA and there's gonna be a little bit of on cheat code and we're gonna do a little bit more of specific types of formulas.
So we're gonna be doing a lot more formula work on this sheet as opposed to VBA so it's kind of a nice change of pace and you get to see something really creative that we can do that you can use in your business or in your home so all I did was I first format of these two rows and I'm just gonna give it a title here something like let's call this yearly activities scheduler and then I'll increase the font and bring it let's see the font we probably put it I want a large fun on this so we'll do.
Something like 22 and then I'm gonna choose something like Arial rounded which is a nice one then one of my favorite ones Arial rounded empty so that's nice and I'm gonna merge the center that maybe all the way to let's just say a H merge ascend to that and I want to put a little icon on I've got one saved here just something and I'm gonna change the color and the size of that so that's gonna give us a nice look not too big just something small and I'll change the color something closer to our theme here same thing with the font give it a give it a nice color.
Closer to our theme like this alright good now we're set up what I really want to do is I want to have the year up here here so we're gonna make C B and C the year so I want users to be able to select from different years so let's merge and center that and then I wanted them to be able to choose the year so let's put that in D and E for merge the center and then on the left I'm gonna create a data validation just something that we can have basic years so you can choose of course you could increase this.
Let's just go to 0 to 0 to 0 to 1 and of course you can increase this as much as you like just going to give it a basic so users are going to be able to choose between the year that's going to be our year in fact I want to assign a name for this right away just so we don't forget we're gonna call this scheduled year let's give it a name rates I also want to give the user the ability to have a start date so in F G and H let's just say F G and H I'm gonna merge this into.
This we're gonna call this a start date the date of the week is important start let's just call it day and then what I want to do is also a drop down list of days of the week so let's merge that so we have enough room because we're gonna be putting in days of the week here some merge the center of this I also want to create a list of days of the week that's very important so let's do it over here let's call this start out with Monday and then we'll drag it down so we can include all the days of the week all the way through Sunday Monday is gonna be our first one and we're gonna put that in AJ 5 through AJ 11 I'm gonna sign a.
RELATED TOPICS:
Named range on that also and we're gonna call that days so as we clear I also
Want smaller days what I mean by smaller like just maybe abbreviation so mo maybe two words tu something like that because those are going to be the headers for our days inside our calendar so let's just create those W E and then th fr-s and then su so what I want that is a lot of small abbreviations because those are gonna be for the day headers inside the calendar I'm also gonna give this a name.I'm gonna call this day abbreviation so let's just say da y a b b because we need to know those because we're gonna have dynamic days so we're gonna have the calendar is gonna change based on that so we've got two of them set up okay let's continue on let's let's color this and colored blue basically what I want to have now is once we have the start dates and I want to put this as a data validation so I'm going to enter data validation here of the days of the week so that users can schedule any of those it's going to be a list and then we're going to call it just days equals.
Days that's the one we just set up now we can schedule it will start it off on Monday why not and we're gonna ready to start building out our calendar now so we're gonna put the first calendar in cell let's try B far let's move this up we've got enough space here let's move this up a little bit too and create them a little bit more space let's put it in b4 b4 okay so we're gonna create our first one's gonna be I want January to appear in here so I'm gonna put yeah supposed to be January I'm gonna put.
That but I don't want just any January I want to actually put the date in here I want to put the date why because we're gonna use that date so for example I want one one and then let's say but I don't want just one one I want to make sure the year is dynamic so how do we do that we can get over the formula equals date and then what do we the date the year is the scheduled year cuz that's gonna change one one that's gonna put us January but now I don't want to show this I want to actually have merchants senator there's all I'm going to use seven columns because that's gonna be.
For the week so we're gonna merge and center this merge and center and then I want to show just the month only so how do we do that we go into number formats we show something like mmm mmm and then for M's is going to get us that that's what I want January okay that looks nice and we can bring this back down to what the others are so we can 3.29 32.8 okay so we get the idea now what I want to put the days in here but those days have to be dynamic right because it's gonna be starting if it starts on Tuesday I.
Want to show T you here if it starts on Monday show em oh here so how do we do that well we created the day abbreviation so we can index it so the first week day would be an index so let's take a look at this equals index what are we gonna index Inc we're indexing the rate we want the abbreviation so that would be the day abbreviation then what is the row number will the row numbers based on whatever they have in the start date right so we need to use a match for that so match what are we gonna match we're.
Gonna look up Monday and we're gonna find it under the days remember we created the days here and we want an exact match so it's gonna be 0 and then comma 1 just the column is 1 there so that way that looks right so if I change this to Tuesday it's gonna change the Tuesday and Monday ok good that's exactly what I want for the first one but what about Tuesday and the remaining days so let's focus on the next day formula it's gonna be based on this right so what's gonna based on the first one because we want it one after that so equals if match we're looking for the.
Mess let's find this Monday this day abbreviation under the day abbreviation right I want to find that day abbreviation this one right here I'm gonna match that we're looking up for that we want an exact match once again if that equals 7 what would that mean that would mean it's the last day if it's the last day right if it's found that it wouldn't be 1 more then it would be this one if it equals then what then if it's in fact and this is Sunday then I want to go to Monday otherwise.
Basically it's the match plus 1 so for example if it's found on Monday it would be Tuesday plus 1 so how do we get that in that case it is index we're gonna be indexing 1 the day abbreviations because that's what we want to find the day abbreviations and we're gonna match the row match of course plus 1 we're going again we're gonna match this day breathe the B 5 again comma with the day abbreviations right we're looking that comma 0 so basically we're looking up Monday but we're gonna supply 1 day.
After match this if we have the match but in this case the row would be plus 1
So we don't want to find Monday I want to find Tuesday in this case so plus 1 and then comma 1 would be the number perfect okay good so we just added the additional parentheses on so now it's Tuesday excellent so how does that work so let's go over that again remember we're looking up Monday I'm looking it up if this day is the last day the next day.Would be Monday right but if it's not the last day if it's a second or third all we need to do is look for the second one the second the one right after the current one that's all gonna doing so if it's the last one we're gonna go to Monday but if it's any other one we're just gonna go to the next one down on the list now we can drag that over to all of our days and it brings out our now we have Monday Tuesday Wednesday and we can Center that now we have it so now let's just change it to see if it's working Tuesday it's gonna go Tuesday Wednesday it didn't work on the last one.
So we have to update that code there why is that so in this case well we have to make sure that this day here B 5 this a K is fixed so f 4 that now when we drag it over we want to make sure that that never changes so now we drag it over it works automatically Tuesday Wednesday Thursday now we change it to Friday Saturday okay good let's get a little bit of formatting now that we got this I'm gonna create some I'm gonna add some formatting to make it look a little bit better so we get the idea we're gonna create that one month and then we're gonna be able to easily duplicate it for 11 more months let's increase this fonts.
Lay something like 14 and I'm gonna give it a format let's drop it in the middle and then won't get as format this I want to give it a white font and then a fill so let's go with a font of white and then maybe bold and then let me give it a fill maybe a dark blue something in the same theme with our current so we'll give it a let's say this dark blue color a fade out and then I want to do the bottom one the one below a little bit the same again white font and then right click and then format the cells and then I was just getting you give it the same I'll give it this one a solid fill which is dis here and then we can also go to.
Bold on this so we can see how our first months can appear that looks pretty good let's make sure everything's centered which it is okay good now we can build it out now we want the total number of rows possible of course we want six total rows possible so let's count down six rows and of course seven over and that's going to build out our total Slits format this I'm going give it a format and then I'm gonna do a raw a border of a solid blue it's the same as our theme right around here but and then I'm going to give it an inside of a dotted line a little bit like this.
Nice okay I'm gonna do the same thing up here I'm gonna give it the format the cells but I'm just gonna use the solid blue here all the way around and then inside we know it's not necessary we're good to go on that okay so that gives us a nice look now we have understand of our calendar it's looking good and we just need to add in some data for the formulas for the day so let's give let's give that a try before we do just fill in the fill in this and this and I spelled that wrong so I'm going to update that pay special I'm gonna paste the format's on that and then let's just.
Merge and center this and then we will change the spelling on that okay good so we have that and also want to give this the same theme so right click format the cells and then the border using that same blue border surround it and then put a dotted line in the middle like that and that's gonna give us the same look perfect that's the way I want merge and center this one and then right justify it make sure this is also right justified okay so we can select between years we can select between the days now let's go ahead and fill in the days on.
This so we need to know based on the days of the week let's put it back to Monday because I'm familiar more with that so let's go and add some code for this what is the first one what do we want to do I want to know if the first day of the month is on a Monday then put one here otherwise put nothing here so that's the code the formula that I want to write so how do we do that we're gonna use the weekday often the week is gonna really help us out but let me familiarize yourself with the weekday so when we use week tick equals weekday and then we put in the date let's just say it's any weekday equals today right then.
What is this is what I want to show you when this is important was it one two three or four look at our date show they start with Monday and they go to Sunday starting on Monday go to Sunday so that for that reason we need to use number two and we're looking for specific days of the week we're going to be using this code that starts with a 2 which means Monday through Sunday 1 through 7 so 1 me Monday - means Tuesday and so on so that is the code so when we use weekday function we always must use the two here okay so that's very important and it's.
Gonna let us know that the current date of today which of course is Saturday is 6:00 Sunday being 7:00 so that's perfect that's what I want to show you so that's important to know that we always use that too and that is why because our days start on Monday so with that said what I'm looking for is January 1st on Monday or not so we can write code to determine that equals if weekday of what of B 4 which is January first comma - remember we're using the 2 for weekday.
Equals what is it equal it equals the start date number let's write some additional code so we can do that I want to make sure that we know the start date number I need to know now I can use match here right I can use match to determine which day but I can do something let's write something even easier that that's automatically done and let's put that right here so I want to know the start date and I don't want to add in a formula and an image so that we can refer to that automatically so let's write that in but first I want to apply a named range to this and we can.
Call that start day so that's gonna be the start day so whatever that changes so now we can add that into formula we can put that up here all the way over here so basically what I want to know is there's a starting one two three or four I want to put it in a numerical format we can do that using a formula equals match start day days right we're looking for the days the days of the week we're looking to see which number that comma exact I want an exact so I want to know what is it so if the start day is Monday I want this to be one let's put that over here if the start date is Tuesday I.
Want it to be 2 so just like that just like that's really important okay what else do we need so this is going to be called the start date in numerical order okay so and now I want to put this in a named range so we can easily refer to it and I'm gonna call that start day number start day number that's important because I want to refer to this start date in the formula or this is gonna change as we change it it's gonna change so that way we can refer to it we don't need to use this match inside the formulas makes our form is a little let's get back to the month so now the.
First day would be equal we can use it much easier equals if weekday and then what does that weekday of before remember that's the first day of the month B for coming to remember twos for our month week it would be this one right here too if that equals what if it equals the start date number equals the start day number then what do I want to do then I want to put in b4 which is the first day of the month otherwise leave.
It blank so basically if it doesn't match its gonna be blank in this case of course January doesn't January with the first is on a Wednesday so it's gonna be blank ok so that's for the first day but what about the second day and that would be for Tuesday the second day is a little bit more complicated because it's gonna be based on the if this has a value or not so the first thing we need to do is say equals if what is it b6 does not equal camp T then what then b6 + 1 right b6 + 1 if it's not empty we notice the.
First Alba need to add one but what if it is empty if it is empty then basically what I need to do is I need to check that if the first day of the month happens to land on a Tuesday or happens to have the start date of this number here so how do we do that same thing if weekday again of what again same thing to start the start day of the month this one right here before January first on the 2 we're always gonna use 2 equals.
What in this case what I want to do is I want to use mod cuz that's gonna get us the remainder so I'll show you how that works in a minute mod mod start date number seven because that's gonna be our divisor seven we wanna add divide it by seven days plus one then before then show so basically I'm determining if the current day divided by seven plus one equals what equals the current week there the first of the month then what I.
Want to do is I want to say okay that's the right day of the month and then in that case it would be b4 otherwise the blank and then double parentheses to close it let's take a look at that again it starts on a Tuesday so that but let's take this a little bit further and that's copy this formula in here I'm gonna copy it now to Wednesday but this one's a little bit different why is it different in the third day what we want to do is we're gonna actually but in this one it would be plus one start day plus one in this case.
Is 4 3 3 1 so this is right now we have the first one but we don't want 4 3 3 a 1 I don't want I want to format this date so let's in fact format them all they're all gonna be date so let's highlight those because we don't want this this is gonna actually show the data if we show the date is it right let's check it out it's right that's exactly what I want but I don't want to show the full date I only want to show the day so how do we do that we're gonna highlight that and go into more number formats and of course we're.
Gonna go into custom actually and then just gonna type D all I want to show is one day and click OK and that's perfect I want to show one and then I'm gonna Center that that's exactly what I want to show very nice ok so what about for the remaining days it's exactly the same except we're gonna change this so we're gonna add two three and four for the Romania's so let's do that let's just copy this and then I'm going to go to the next one and then instead of plus one I'm going to change this to two ok very good but we do need to update this this of course is no longer we need to.
Make sure that this is gonna show c6 it's actually going to be c6 because it's the next one over and change this to c6 and now what we can do is we can drag this over but we need to make sure that b4 stays the same we don't want to interrupt we don't want to change before so it's got to be changed at the c6 but we do want I don't want to keep it because I want to copy this for all them so what we're going to do is we're just going to make sure that we update it c6 so down update it to c6 and then what we're going to copy this and I'm just gonna update it just for these rows here.
And then I'm gonna make this of course d6 and then update this to +2 that's it that's all we have to do tab over that same thing paste it in and of course in this case it would be a 6 so it just changes to e we're almost done here and place this two numbers we're gonna add three good and one more time again in this case it's going to be f6 f and it's gonna be different for the formulas F and then change this to four and then we have five and then the last one is going to be five so this one's.
Going to be based on g6 which is the cell below it g6 change this to add five and that's gonna get us our first row of dates perfect and let's change the start date to make sure we know it's working exactly and four start dates on Wednesday perfect that's what I want I want those days to show no matter what the start date is so what about our remaining rows our remaining words let's take a look at that in this one basically what I want to know is equals if and this is not necessarily gonna be.
The case but it's easy to copy down this formula this equals empty then of course make sure it's empty that's the first thing we have to do also but this formula wouldn't apply necessarily to this sub this can apply to every so it's really easy to copy and paste this down because generally the eighth day of a month is never always never gonna be blanked but in this case it's easy to copy down so what do we want to do if it's not empty then I want to run a check if what I want to do if what if the day before and the current day.
Aren't same months then leave it blank so how do we do that if the month so we can do that the month of h6 in this case h6 does not equal the month of h6 plus 1 H 6 plus 1 in that case what do we want to do we want to show blank blank nothing because it's not different month otherwise assuming it is the same huh all we can use h6 plus 1 and then close the parentheses.
Close parentheses one more double closed and enter perfect that's what I want so I've got the six what about the next day the same thing pretty much the same thing we just need to change the cell so let's copy and paste this and instead of h6 we're gonna be it's going to be considered b7 so instead of this we're now focused on b7 b7 and now we can easily cop get over b7 again and then change this to b7 one more time and then b7 okay so.
Now we've changed them all so now we know so now all I need to do is copy we're almost done with the formulas here and then paste the formulas across paste those formulas now I can copy an entire work week copy that and paste the formulas all the way down here and paste the formulas perfect that looks really good let's just change the date Tuesday nice very good so now it's starting to come together now let's work on a little bit of the formatting I want to make sure that the formulas are correct once I have the formulas once I have the.
Formatting it's easy to copy and paste the different cells so let's do that all right the fonts are a little bit small what are we updated to let's say twelve one up bigger and make sure everything is centered in the cells just the way I like it now what I want to do is I don't want to show white for the days that don't exist I want to use conditional formatting let's apply that so what I'm going to do is use a conditional formatting and I'm gonna create a new rule and I'm gonna use a formula basically it's if it's blank I'm gonna format it and just give it that.
Blue color the blue background oh we're using it so there we go that's the way I like it now let's add this blood blue here okay good it's starting to come together I also want to create an activity schedule here so let's do that I want to create something called I want the activities to appear here so let's do that call that daily activities and then I want to put the date here a specific date here of the activities now I want the activities to list down here so let's just put in temporary date here but I want it to be a long date I want it I want it to show the full day so a.