On Monday Tuesday or Saturday we can change the start time as well we can also change the interval too so if we were going to go ahead and change to 15-minute intervals or we can change it to one-hour intervals and we can also change the scheduled year as well okay so let's go ahead and get started and show you how this is done all right in this particular training we're going to spend a lot of time talking about.
Named ranges and those named ranges such as months right or weekdays okay and those are used throughout our formulas and they're really really important here because they really help us with our formulas and they help us to code faster as well so we're going to show you how those name ranges are used both in formulas and in VBA cody's so that we can rapidly create accurate code that helps us get our products to market and to the customer as quick as possible okay so let's get.
Started so first we have to define multiple types of named ranges and if you look in the formulas and named ranges here you're gonna see several in fact let's say there's so 10 or so named ranges maybe 11 of what we created so let's go ahead and go over those so we can start to define of how we make the foundation of this application okay and first of all I've created a list of months this is a very simple months and to do that I've created that and.
Remember in in Excel we can do that simply by just clicking and dragging down okay so it's very simple just like that only do is start out with the beginning and then I've gone ahead and created and then what I've highlighted this right all I did was enter the months here so you could just type it in months right if your name is now ranged and that'll that'll define it I've done the same thing with days as well right start off with just a single day typing it in right double-clicking oh that's too far okay let's go down to back to Saturday okay and that's it and.
Highlight that and I've already defined it for you but you could also highlight all and then type in weekdays okay so we've gone over months and weekdays also if you want to change the name it's not gonna change here okay I don't believe so it's not gonna if you do want to change the name she'll need to go into formula named ranges and then go into here and then click Edit and then you could edit the name here okay if you want to change the name so you have to do it from the name bridge okay and so we've got months we've got days we've also have times and now what I did is I've created a times and I want to give.
The user the ability to add in a start time right and I want to include basically all times at 15-minute intervals okay so define this list I've created a time list okay and I've started at 12:00 a.m. and then the next one is at 12:15 and 12:30 so the quickly a way to quickly create that is just simply by let's go ahead that simply by highlighting this and then extending this down as well so that.
That's world and the format let's go ahead and take a look at the format that we use cuz in excel the informant is not in here we'll have to set that as a time format clicking here time and that's going to include the seconds I don't want to include the second so I'll go ahead and click on more number formats and then I'll go ahead and find the time that I want without the second so we're gonna use this if you're on military time you'll probably want to select this okay so if you if we're just like this you'll see that changes it to military time but we don't want that so okay so.
We've got time to defined and if you'll control shift down arrow it'll highlight all the time can you see times has been set okay so that's helpful for defining our time so we've got time and now intervals let's go ahead and take a look at how did I get I want intervals right because I want to be able to do user to set how often this schedule and I want them to change be able to change that within a specific range I want to have that in a dynamic.
List here okay so fifteen thirty and one those are the time that I want now how did I create that well we know bright that a day is one right in Excel one is a day okay so that means that one hour is one day divided by 24 okay if one day divided by 24 that's the number of hours and it's basically a decimal format right and I've reformatted it so if we take off that format right and we go back to general we'll see it's really a decimal right but in excel we have to change those to a time format okay so.
Remember one is a full day so anything less than a full day is going to be a decimal number okay less than zero okay so it's important that we know that so to get to one and now of course to get to 30 you can do it in several different ways it could be one divided by 48 or we could simply divide be 25 divided by two which is this one here divided by two and then 15 minutes is this one one hour divided by four so I've chosen to show.
You that and then I've named those ranges when we highlight those we'll call those intervals okay so those are intervals
And then next up we have the Oakland Center that so I'll make it clear for you and let's go ahead and Center this one so that's also clear and back down to just one row okay and I wonder if I want to make sure those are very clear so that how we've highlighted this well I'll go ahead and darken those a little bit so we can see those column headers a little bit differently and that's clear okay and then the same thing with you.I've just listed a bunch of years and then I've gone ahead and named that range years and now to create that drop-down list all we need to do is go into the data fields here data go into data validation and then click on data validation and then we just always select list right and then we select weekdays and this is remember this is the same thing if we were to if it's the same thing as doing this okay but automatically you see how weak this comes up because we've already named that range but if we were to change it and extend it it would be back right so.
You see it knows Excel knows that when we've selected the exact name range it's going to automatically put in so you can highlight that or you can also type in weekdays okay and then and then to check to make sure tap out tap back in okay tap back in and you'll concede the dancing ants around that that shows you that you've got the right data selected so that's a great way to verify your work and I've basically gone through the same process for the start times and.
Through the intervals which we just discussed and the years so this enables us to quickly set up now how did we go ahead and create this calendar well why don't we go ahead and take a look okay now because our first day is dynamic remember our first day is dynamic weekday start here if we were to put Monday right Monday okay so p4i this is.
Equal to P for so simply this is a link to this right here okay so now how do we create Tuesday that the reason is we can't just assume that it's going to be we can't assume that it's the next day right because if it's if this is going to be Saturday then we need to go back up so basically we've said I just created a simple form that says if d4 is Saturday then make this Sunday okay if it's not then what we're gonna do is.
We're gonna index weekdays so weekdays right you know is this you see the Green weekdays that's already highlighted match okay whatever want to do is match I want to match Monday I want to find Monday right where's Monday okay and then I want to drop it down one more so I want to find Monday where is it right what row is it on and then I'm gonna add one because I want to 'zed a right so that's all we have to do using index matches very powerful let me go over.
That once again we're gonna index weekdays that means we want to find something within this week is okay so with index we need to find two things we need to find one we need the row and two we need to call okay this this here is the row okay this here is a column now we're gonna stay on the same column so the columns easiest one but we have to find that row we have to find that row and the row is simply this locate Monday and then add one right locate Monday add one okay so that's all we have to do and.
We've gone ahead and used that index throughout it so I've just dragged and dropped that okay so that's all we have to do for that okay so it's very very powerful and so let's go ahead and set this back to Sunday that's what I'm used to and you'll see I've already set the calendar to refresh automatically okay so now we know how we get all the days of the week dynamically okay and so what we need to do then is let's go ahead and go over the formulas to get the calendar.
Okay okay so basically now I we remember we discussed how we named these ranges but I've also named the individual field here this field is called start time okay so that means when I type in 9 o'clock equals start time okay I'd like that it's not necessary we could simply we can easily do equals this okay but we've named the range because it's nice to see that I like I like to see names in my formulas because they really help me define when formulas get complex right when you see those names inside it's really easy to.
Understand what's going on so I just urge you to name your fields even if it's just one field or one cell
Sometimes it really if you know it's gonna be using a formula or in VBA let's go ahead and name that okay it's really helpful moving forward okay so back to this formula okay so what we've done is I've said if the weekday okay and now I want to know also one more thing we have to know what weekday this is okay that means basically what I.Want to say is if the first Sun if the first day of the month if the first day of the month lands on a Sunday then put that here if it's not leave a blank so that's what we want to find out if the first day of the month lands on a Sunday then put the day here if it's not leave it blank okay so that's the idea now the problem is that the complexity is this is not always Sunday right because this could be whatever they set here okay so the.
Reason is what we have to do is I have to say if and basically what I've done is just put a formula down here since this is the first day of the week so I've used this formula a little bit of a helper side it's not it's not necessary we could we can actually put this directly in the formula but I wanted to separate them show you so what I've said is d4 d4 right what is this is the first day of the week okay remember when we use the match weekdays okay this is our.
First day there's a few different ways to do that but we know this is number one okay two three so we've gone ahead and run this formula and this helps us out so remember D 42 is the day of the week and this will change right if we change this if we change this to Wednesday you'll see the numbers down here change four five six seven and then it goes back one two three so you see these numbers change based on the day of the week and that's going to help us moving forward so let's go ahead and set that back to.
Sunday let's go ahead and increase this column a little bit by double clicking back to Sunday we go alright so let's go ahead and start out so basically what I'm gonna say here is we're saying if this date now the date is the scheduled year the selected month now the selected month is 1b for okay let's go ahead and take a look at that B for selected month we've put that month in here now when that month changes this this will change okay - if it's - if it's February is gonna be -.
March it's gonna be three etc okay so if the date the scheduled year the selected month and the first day of the week right if that equals if the weekday the weekday right the weekday of this date equals D 42 equals this number down here remember then put in the date then put in that date okay then the date is the first day of the year if it's not on a Sunday then leave it blank or not on that okay so I've done the same thing.
Over except I've added something before if this is the first day of the week we don't need to test anymore we don't need to test this condition so if this is not blank then all we needs to do is add one so here if d5 is not blank then it's then we know then we know what the next day is it's going to be D five plus one okay if it's if it is blank then we also have to test is the first day of the month landing on a Monday so is the.
First day of the month equal to e 42 in this case right remember if it's two if it's two if in this case in January it is right so it is so that's in this case it does land on a first okay so that's a really really important fact that we want to make sure that we we use so so then all we've done is on this first row we've just run the same formula over and over again okay so it's the same formula and then it's easy after that point all.
It is is this date plus one the plus one this plus 1 okay so we just go through now towards the end of the calendar on the last two rows it gets a little bit more complex again because what I want to do is I want to find out is basically is what I'm saying is if this de plus 1 if the month of this day right now we know our selected month is as 1 right our selected month numbers 1 so what I want to do is I want to test a.
Condition and I'm gonna say if this if this dates remember these are dates let me just show this to you again because I'm sorry it might be a little bit confusing let's go into home and then this is a custom format okay this is a custom format so in actuality if we if we show you their normal format it's gonna show up as date you see those are all dates right however I'm only showing the day ok however it's a custom format so if we go back into manage formats and.
I'm only showing the day okay I'm only showing the day D that's it okay and that's just gonna show the day so by doing that it only shows the day we don't need the month you could if you some people like to show a month you know you could you could on your calendar you can change that maybe you want to show a month maybe you want to show them like a three digit month you could go D - em D - mmm that would show that would look like this first of January you see some people prefer that you could do that as well if you like so.
That's why only numbers show but in actuality these are all dates okay just so you know they're they're all dates but they're shown but only the day is being shown okay so that's an important understanding as we move forward so back into this so we're saying is if this date plus 1 if the month of that is not equal right is not equal to the selected month then blink that means it's a new month for example this formula here there is no there is no 32nd of January right right there is none so we're.
Saying is if if the month of G 29 + 1 G 29 + 1 right in this case it would be February 2nd plus one is for a second so that means it's not the selected month right G 29 the month of G 29 plus 1 is 2 the month of G 29 plus 1 is 2 it's February right so that's not equal to the selected month so in this case it's blank okay so in this case is blank and basically once we know this cells blank then we can test every cell after that all we need.
To say is if this previous cell is blank then make this one if the previous cell is blank then make this one blank so that we move forward throughout okay so that's how we do our calendar and we also have conditional formatting as you've seen I've gone ahead and colored the current day the current day Orange as you can see and that's done through conditional formatting and let's go ahead and take a look at the formatting that we have we've got on the headers on the headers we've got two different rules one gray if it's blank okay.
And this is really important because I don't want you to work too hard so on how to create these okay so for for the today was easy because there's no all I'm doing is testing for today so all I did was was highlight the whole the total table and then I just added a grool you know conditional formatting a new rule and then I just put format cells that contain right and then I just dates right and then today so that's all I did and then I formatted it so that was very simple because our I guess I guess if we.
Put a date in here with probably 122 to 0 1 8 right it doesn't work because I have a pre I have another formatter right so I have the green format which takes precedence is on the selected cell and I'll show you how that works so basically all we've done but now for this I've done two different rules because I want to show for anything that doesn't have a date I want to gray it out okay so I've used two different rules one is on the headers and so all I.
Did was highlight right holding the ctrl down highlight all of the affected rows okay now this is really important because I don't want you to have to create conditional formats for every single cells let's show you how to do them all in once control number I'm holding none in control now and I'm highlighting up I messed it up okay let's try that again holding down the control okay and we're gonna highlight every row that.
Contains a date okay and now all we have to do is go into home conditional formatting new rule and we're going to use a we can use a formula okay and this is important we have to make sure that the first cell that is the one that we're gonna just defy and when we change this to d5 we want to make sure that there's no dollar signs we don't want an absolute cell because we want it to affect each one of them now keep mine something strange is gonna happen and.
I'll show that okay what are we gonna format let's just say let's just form in a green color because I want to show you something that's different I'm gonna show you how that works okay and then d5 equals blank okay another ste five is gonna change for some reason I don't understand exactly why it will change but okay now go back in there conditional formatting manager rules and make sure that everything said oh so it looks good okay so d5 so you see we don't need two rules but I just wanted to show you how that's going to.
Work okay so d5 is Mike remember no absolutely no dollar signs cuz we wanted to affect only the individual cells so you see how that works okay and let's go ahead and back into here and let's go ahead and remove that rule we don't need that but I wanted to show you let's go ahead and delete that and so basically I now for this cell here for these I did the same thing right because I want I highlight using the control highlight every row and then in this case it's going to be d5 as well.
But just make sure that's not an absolute no dollar signs so that's how we create the in conditional formatting and now we did through some code I want to know what the highlighted cell is okay I created additional right if we click on a cell I want that cell to turn green okay how did we do that all right back into conditional formatting manage rules and you'll see this green here and I've done the same thing d5 equals M 3 M 3 okay let's go ahead.
And close that out right d5 because I'm three how did I do that M 3 is the date the current date right so basically it's saying if this date equals this date then color this and how did I do that same process highlighted all these cells okay just like this scroll down holding the ctrl holding the ctrl ok right ok conditional form any new rule okay use a formula to determine that yes okay select the first one d5.
And you can use f4 f4 f4 f4 that'll remove it ok that'll alternate between just clicking f4 alternates until you find the one with d5 that's kind of a quick link using f4 format that alright let's make that red so we can see okay and and what do we want d 5 equals okay D 5 equals M what was it M 4 okay and M 3 M 3 M 3 will never change right because we that's always going to be that so that one cannot be can be.
Absolute and does contain the dollar signs before the MN before the three so that's very important ok when we click OK okay and let me just fix that and so that is going and watch I want to show you something that happens Tennessee look at this you see that that happens a lot when the cell does not include it all we have to do that's very common because we just I don't know why but it happens all the time go back change that to 5 why does it change good question somebody must know somebody put in the comments why that changes it always.
Changed back ok apply and it changes okay all we have to do is the reason it changes the reason to change here not here is because you see d5 was located in that d5 was located in that so it does change but you might want to change it back but it's not absolute it tends to change with the cells or it adds them up something some reason just make sure you know it's normal if for some reason it doesn't work go back and change that back to the way I was and everything will work out we can delete that rule for now so let's go ahead and delete that so we've shown you.
All the conditional formatting that works within this calendar here we do have one additional conditional formatting and that's simple and all I've done is I've if this I've added I can just format if there is any value in here use this faded green okay so that way I've got that conditional formatting and there's no VBA that these users can justify panic so it's really simple all right so let's get into the nuts of this and see exactly how this is working.
Under the hood so we can go and see how did we create this calendar all right into the VBA model we go and you can click on the file if you don't have VBA options to get to that developers tab customize the ribbon and click here for the developers tab if you do not have it visible you can go in here and the developers tab click visual basic or alt f11 will also get you there let's go ahead and see what we have under here we've got a little bit of coding on the.
Sheet itself and we have some macros that help us work not too many and I'm gonna walk you through those so don't worry too much all right so we have a few macros our first macro is when I click on a selected cell I want a few things to happen right I want that day to load I want all the schedules to load okay and now remember Excel only holds a value so the actuality is the data the.
Scheduled appointments are not being held in here they're being held somewhere else as soon as we click on that and where are they being held well they're being held to here in this database right in here and I've and I and we have a new one for each year but I'm going to show you how we program that in automatically to create it now watch let's go ahead and create an appointment for 2019 all right change the date on that and then watch the magic happen now we have a database for 2019 just like that nothing else to do.