Free each and every week so i really appreciate that all right let's get started on this training because i've got so much to cover this type of application you can create any type of scheduling application so you can create this customize it for your clients sell these either way this is a really great foundation because we're gonna not only be able to show you how to scheduling how to avoid conflicts how to create a drag and drop scheduler how to add pictures of your staff in there how to set things up so you have a dynamic setup screen so you can show what you want to show on those drag and drop but simply by selecting or unselecting that having a.
Dynamic start time intervals a booking type like chair or bed or room or something like that could be anything and also we can have dynamic of course rooms or chairs whatever you want on here so if i were to change this to a room if you want to book a room this is going to change room all you need to do is just change them down here so very very dynamic application and that is the key here customizable tax figures a lot to show you so this is a lot of foundational principles that i'm going to teach you in here even if you don't want to create your own spa salon manager.
The things that you're going to learn in here the scheduling the invoicing the formulas that make the drag and drop the editing everything is going to be really really foundational so you're able to create your own applications regardless of what they might be and then of course sell them for passive income that is my goal here that is why not just to teach you excel but to make you successful with excel this application simply is a drag and drop scheduler for the drag and drop it's going to move that appointment update the stats and everything like that you'll be able to edit the idea you can change the the primary service if you have different services and i'll save and update that colors are dependent based on service and that means when you have service items you.
Create different service items we can also assign a color to that so we can assign a different color and so for each let's say we style 24 cut and style whatever service you're providing you can assign a color to that if we assign a color to orange that color is going to be reflected inside the appointments we'll be able to navigate based on the days and we'll also be able to show you a lot how to do that also be able to add brand new appointments over here and i'm going to show you how to create this custom background we'll be able to show you then how to edit it just a one click to edit that appointment unhiding some rows to allow us to edit that and also creating.
Showed you briefly before the setup screen pretty much it has the ability to create customized user experiences so we want to know what if their start time is nine what if it's seven o'clock right and that's really helpful because we can start it earlier or later or however you want and it's gonna be based on those times those intervals so it's based on whatever times you want to set up in your application okay so we also have intervals maybe you want those intervals every 30 minutes right so we can set that and as soon as we do that and refresh the schedule it's going to update automatically on the schedule so now you see those appointments are all condensed right there every.
30 minutes so these are shape based and they work really really well with that kind of interval so we can customize the scheduler any way we want it all right so back in the schedule we'll set this back to 15 minute intervals this is what we had it but it's very customizable we have uh then again our customized right if we were to change this to let's say room one that's going to go ahead and change on the schedule so now we see we've got room these are basically using formulas that we're going to go over so it's very customizable in that way and of course also we have now the.
Room one is available in the drop down list as we add those things to the appointments right so whether even if you're using meeting rooms or anything like that there's a lot of things you can do with this any type of scheduling application can benefit from this kind of application okay so we'll go ahead and set that back to chair one course you can call it anything um our sample today is going to be with a salon so we also have the durations right how long are those appointments these durations are going to be based on our intervals so if i set five minute intervals i may want five minute durations right if i set it back to 15 we're gonna have 15 minute durations.
Interval intervals within 15 minutes 15 to 30. so that's all based on some formulas okay so we also have an appointments right an appointment stable now this is simply where all those appointments are stored we have an appointment id a client name a date the date scheduled the start time what time is the appointment started how long will that appointment go for and along with the end time i want to know the staff who's been scheduled on that what we're going to call this booking type but it's also known as chair notice this is also based on the booking type so if your booking type is room or bed or whatever it is it's going to appear here so we're going to call this booking type for our purposes okay so once we have the booking type we also want to make.
Sure that we have the primary service what is the service that we're going to offer our customers right we also want a secondary service maybe they want two different services so we can put up to two in this application of course if you could create this you can add many more if you like okay also what i want to know is some notes perhaps some notes we're going to put on that so that's all based on the information that's entered here inside for each appointment when we save an appointment those lines hide and we also have a nice beautiful schedule that we can see we can also see that our staff pictures are displayed so when we go into our service items here we have.
Posts Related:
An item id these are the service items these are the service items that we're going to be choosing when we add a particular appointment it is this list of service items here and this list
Of services items here the same list of service items that we have here we also have a description this item description will appear on invoices the duration what is the default duration right this is very easy when we create it if we create add a new appointment and we decide that we're going to give it a specific let's say a primary service of wash and dry we want to know what the duration is of that what is the default duration of that so that way we can simply add a service.And then we know the default duration so that's why it's helpful to add that duration i want to know a color we're going to show you how to create this little pop-up color that automatically changes based on you know style the cost of it we're not going to use this too much but i put the column in there because it might help you understand when we create these invoices what is the overall cost of that and then a price that's going to get transferred over to the invoice we also have a staff list here just basically an employee id a phone number email we're not going to use too much of this really and but i just kept it there large and but most importantly we are going to use this staff picture now that staff picture have to be i got a lot of questions hey.
How come my pictures don't show up right make sure you watch these trainings of course that's first and remember this staff picture here this is just a picture name right the only way that we can insert a picture as used because when you get this application you download it you're not going to be able to see these staff pictures why is that because you have to set your folder this is where my picture folder is located right here so when you combine this folder path for me it is this location right here when i combine this folder path and i make that combination with this file name picture here inside our staff with this page name the combination of that path will create the.
Full file path of the picture i can then load that in so make sure when you watch these videos and you understand that that's why if you're not seeing them you know a lot of people get this and they say hey the pictures aren't showing up well that's because we have to make sure that we create that full file path and then of course on our patreon platform if you want these pictures on our patreon platform i provide all the icons i provide all the employee pictures all the resources associated with every training is available on our patreon platform so i hope you'll join us there all right so we have our staff and we also have the invoice what is an invoice screen all the service items we have our client we have our description of that service.
Item the quantity the cost and the total we can save new invoices we can load in invoices we can update it and save and load it so it's really really helpful we can create an invoice in just one click based on that schedule so when i click on a specific appointment here and i click this little dollar sign right here it is automatically going to create that invoice if i save that invoices it's automatically going to be saved i'm going to walk you through that we have an invoice list this is the list of the invoices i just created all these invoices here the date of that we probably don't need the time there that's just a format let's go ahead and update that that format should be a short date format okay so we've got a date the customer the.
Staff that's associated the staff is also brought over from the schedule right so if we have a staff of greg perkins right and we create an invoice for that it is that staff that's going to be brought over into this so it's going to be brought directly over and the total there we also have an invoice item these are the individual items from every particular invoice i just created invoice number four that invoice had two items and the invoice item name description we have the quantity the price and the row associated with the invoice notice this is nine and ten if we.
Look back on the invoice we see that this is row nine and row 10. so we need to know what when we need to load that up if i want to load let's say i'm going to load one up and then i want to load four up that's the one we just created i need to know that they need to come back to row 9 and come back to row 10. so i'm going to show you how we do that to make sure and also need to know what database they were saved on that's 11 and 12. notice we also have in our invoice item 11 and 12. that is the row 11 row 12. we need to know what row they're saved on so great that's pretty much it that's the foundation of this application that's everything that all we need to do to.
Build it so how let's get into the intricacies to see exactly how we did that of course i want to take you a little bit through the setup screen we went over but i just want to go over some of the named range that we've created those named range is going to help us go on we have a start time this is the start time i'll probably probably create this earlier 11 if we go into the data validation data validation and we see that this is based on the times right so this is based on all of these times associated here every time that we associate here so we can create that i will probably make an update so we can add additional features we can create that based on maybe a time in the setup screen a list of times so they can be customized okay so keep that in mind that's just based on that and also what we have is we have an interval of 15 minutes five minutes.
And but what i really want is not this interval five minutes or ten minutes what i really want is i want the number the decimal number that's associated with 15 minutes which is this now how do we get that well we're going to use these intervals i set up this little table now you've seen this you may have seen this before in other applications that i've created so basically i want to associate a decimal with a specific amount of time how do we get to these well if we know that in excel one day is one right so if i want to know how many what is the decimal.
Associated with one hour all i would need to do is do equals 1 divided by 24 and that is going to
Give us that .04167 now what if i want to know the 30 minutes why would you simply divide that by 2 and that is going to give us let's try that again equals this divided by two that is going to give us our 30 minute intervals and i just keep doing the division you know dividing it by two right and then this would be six in one hour ten minutes of six so i divide that by six as you can see right.Here one divided by 24 divided by 6. so what i want is the decimal that's associated this every 5 minutes is 1 divided by and then 12 because there are 12 5 minute intervals inside a single hour that is going to give us this time right here these decimals because times in excel are based on numbers or decimals anything less than one day is going to be a decimal so what i want to do is i want to determine what this 15 minutes is what is the interval that's associated with this 15.
Minutes how can i do that well i can use that with an index so what i'm going to do is i'm going to index all these intervals here g and then what i'm going to do is i want to return the match based on c4 what's in c4 that's what they've selected here let's go into this one here so you can see c4 is going to look for c4 it's going to look for here once it's found it's going to return the decimal associated bet using a match located in column f so basically we are going to return point zero one zero four that is the one associated for fifteen minutes it is this one that i've called the name bridge called interval interval that's the named range so when i create these durations.
Here all i need to do is say equals interval and then all i need to do is just equal whatever is above plus the interval now the difference here in this list is is the formatting if we look into the home and we go into the custom which is our we see that this has been formatted still the same decimal number but this has been formatted with this custom h colon mm that is a custom format that's how we get this duration i don't want times i just want the duration shown in hours so that's how we get this but it's also right if we were to change that to general we would see that it's still that decimal that's showing up it's just based on the format that we've set it up okay so.
That's going to give us our duration our list of durations it is this that we created named range so inside the formula name manager all the way at the top duration here let's take a look down here we go duration here that is going to be basically to set up everything that's going to be based in this column so that's all the durations we have so we've created an image called duration so it is this same duration that we're going to use directly inside here when we look in the data validation here and data validation you see it's duration so that's how we get that duration our.
Time it's a data validation of course based on that time so we already saw that that's going to be called the times that's going to be based on this time so continuing on the setup screen so we understand that and then we understand the picture folder i also want to know inside the invoicing are we going to be charging sales tax or not so if we are then we need to make sure that we are if it's yes or no i want to base this dynamic and if so what is the percentage so if it's no on those invoices i don't want to show any sales tax however if we do say yes and i want to and we do set a tax on the invoices i then want to show the tax amount and show the tax.
Information so that's of course dynamic as well all right so that's pretty much it for the setup screen and then we have our dynamic remember this here is going to be based on the booking type plus i'm going to add an s to it so if i put room here or if i put bed here or if i put any or stall or anything like that it's going to just be plural here so for example just change room here it's going to be room so that gives the user a little bit information on what to put in this column so that is all your information or that's how we're going to call that the booking type what type of room what type of chair are we booking what type of you know what do we need okay so that's it for the setup screen relatively simple not much going on here you can add to that of course there's a.
Lot more you might want to do with that but that's all clients is relatively simple just the table inside the scheduling we have some information here that's going to help us moving forward because we've got also some named ranges and some formulas here located in columns a and b let's go over some of the basic named ranges so we can get that out of the way so i want to make sure that we understand so we have an appointment booking type now the appointment booking type is based on the ap so we have appointments we've got several named ranges and they're all dynamic name drains using the offset we've got one for booking type this is going to be book and type remember it says.
Share again we're using a formula on that header based on the book and type we have the appointment date here we have the appointment end right i want to know the end time we have the appointment id also very important let's just close this out no i don't want to save the changes and i'm going to go back in the appointments let's continue on so it doesn't keep switching screens here so we also have the staff that's associated with that that's going to be really important because i'm going to need to know are are there conflicts are there staff columns so i've created a different named range now a lot of you have asked me randy why don't you just use tables because the name ranges are created automatically i really like i'm very very picky and specific about how i create these.
Named ranges and what they look like so i really wanted things to look the way i you know because when i put these in formulas it's very easy to read and they're very short formulas and they're very easy to read and so i just like to have my own that way but of course if you're comfortable with using cables and you enjoy using tables and they're they're good great you know that's that's as long as it works for you that's fine right i'm just showing you an alternative way it may or may not be better for you but i'm really i really like to work with these ranges i fi i find that tables are limiting so we have the appointment star tape and then back into the booking type we've got booking type i showed you that already so we've got that inside there let's go back inside our service items we've got a few we've got item id also.
A named range okay and item name right item name also in name range we can assume they're all based on offset and that's going to be based on this item name right here so we went over that so it's called item name data validation we've got two fields that are associated with this called service name service name we're using okay service name and that's going to be the same for both the primary and the secondary and then just some nodes fields there so what else do we have we have a few others but we'll get into them as we need them so we don't go over all the name ranges so i've got a client appointment date appointment time duration and i also want to know what is the.
Selected appointment id remember each individual appointment has a selected id that's how we can keep track of them and when we select a specific appointment i want the id of that to appear right here i want the id whatever that ideas appear then i also want to know what row is associated with that appointment if we appointment uh for example if it's appointment id number three it's this is row number six our first one starts in row four so keep that in mind so to do that what we're going.
To use we're going to use a match and we're going to base it on a named range called appointment id whatever's in b2 i'm going to run a match and if there's a matches found i'm going to add 3 onto that right because our first one starts in 4. if there's no match we're going to show a blank that is going to give us the row number and that's how if we add a new one right let's move over here maybe we should move that button over it's a little bit farther over here a little bit too far to the left here so we can't see it okay so if we add a new appointment here that's going to go blank because everything's right there's no longer we've cleared out the appointment id in b2 now it cannot find an appointment id therefore it's showing blank right it's a brand new one we've got some required fields if i try to save this of course it's going to let us know we've.
Got required fields i also want to know the next appointment id the next appointment id if we take a look inside appointments all of our appointment ids are numerical the next one would be 15. we can use that if you see my videos before we use the max formula max of all the appointment ids plus one if there's an error why would there be an error will there be an error if there's no data at all then i want to revert back to the first available which would be one the first id then what i want to know is the end time what is the end time i want to calculate and if you'll notice here on a selected employee we've got a we've got an appointment time and we've got a duration but there's no end time here but i really want to save that end time and that's going to be.
Important when we determine if there's conflicts is there is the staff occupied at the same time or is the current booking type in this case chair is the chair octane right if i try to move this right over the top of this one it's going to let me know hey this chair is already occupied for this time please select another time so we got to select another time you know we can't do that right because that chair is occupied so i need to know that we're going to use a formula for that we need to know if there's conflicts so again that's really really important when we can do the same thing with staffs so what i want to do is i want to know an end time and i want to save this end.
Time inside the appointments database so i want to save it right here but to do that what i want to make sure is that we don't necessarily need an end time we don't need the user to enter the end time all we need is the appointment time and the duration the end time can be calculated and it is simply that it is simply the appointment time plus the duration right plus the duration that's going to give us our appointment time so to figure that out we could do equals right 8 a.m plus.
One hour just entering that's going to give us that end time and if we format it based on the time it's going to be like that that's all we need to do because what i want to do is i want to figure out hey is there a conflict right is there is that chair occupied from the appointment time from the beginning time to the end time so end time is a critical component of that okay so then what i want to know is i want to know uh that have all of the required fields been filled in when i click add new i have the six fields that are required if i try to save it i need to know hey.
All these point the fields are not filled in let the user know that these fields need to be filled in the best way to do that if we could do if d is blank or f is blank or h is blank it's a lot more complex in or just more time consuming more code writing but however if we can just create a specific formula that is going to let us know how many of those required fields are have been filled in so we can use that right here required fields well all we need to do is just use count a based.
On all of the required fields if that is less than 6 which then we know that we need to let the user know that all the required fields have not been filled in so when we select an appointment we see that now six have been filled in so we know that we could save this those required fields so we will make a check inside the code if b6 is less than six then let the user know that all the fields have not been filled in so to put that i also want to know is there a conflict right is there a conflict between the chair we'll call it chair now notice this is dynamic it's booking type.
And conflict so if you change it to room it's going to say room conflict which is kind of helpful okay so keep that in mind now what i need to know is i need to simply add up and we're going to use sum product for that and we're going to use some named ranges inside that so let's take a look quick look at these named ranges just to double check before we go into that i want to remind you as we use it so we have appointment staff appointment staff it's all the staff associated appointment starts all the start times appointment and here's all the end times this is where that end time is going to play into it and also we have the appointment date so we're going to need all of those things the first conflict that we're going to check for is we need to know if there's a conflict and i need to know if i try to take this appointment here and i try to drag it over right in the middle of this one it's going to let us know hey this chair is already occupied for this.
Time please select another timer chair right and so we have to then just change that to chair two or change it to another chair and save those to make sure that it gets changed back okay so keep that in mind of course we can move it before which is not a problem right so that same but it's just the so we have a chair conflict so when there is a chair conflict we need to let the user know we could do that with this formula right here if this particular number here is greater than one greater than one that means there's a conflict how can we do that well we're going to use some product for that i need to know if there's more than one count of the following if the appointment date is equal to f2 right so that's our appointment date f2 is located right here.
Inside our appointment date here then also what i need to know is i need to know if also the start date is less than or equal to h2 if the start date and if the end date is greater than or equal to h2 and if the appointment booking type is equal to f3 so if all of those instances so what i basically want to do is i want to count all of the instances when all of those conditions are true it's just one right now right just this appointment but if it's more than one what i want to do is i want to.
Let the user know the first thing what i want to do is i want to add some conditional formatting onto here to let the user know as you saw there was a problem right when we moved it down here take a look at this all of a sudden this number became two now there's two appointments that occupy that time slot they're on top of each other right so we also want to add some conditional formatting in here so when we go into the conditional formatting and manage rules it's a very simple conditional formatting and we're going to edit the rule when b7 is greater than one i want to give it this red background with red font so they'll let the user know that there is a conflict as soon as they make that change and then save those changes the conflict is gone so that.
Is what we call a chair conflict or a booking type conflict we have another type of conflict right we also want to know is the same particular staff does the same staff have a conflict are they occupied at that time we have a staff conflict right if the same staff is also going to be doing the same thing if we take a look in here we see k hopkins on this one you see there's a conflict all the way over here on the right here in chair seven there's also k so take a look at this k.
Has an overlapping time right so we we see that there's a an issue and it's going to let us know if i move this down where she has availability here we'll see that that conflict now we're going to click on that and click on the edit there and we see that that conflict is gone so we see that k here and here there's no longer conflict and we do exactly the same thing on the staff conflict the only difference is appointment date appointment start and appointment enter all the same the only difference is this time we're counting the staff based on what's d3 that's going to let us know if.
There's any conflict so if there's any more than when all those situations are true if there's any more than one it's going to put two and that's also going to put a conditional formatting here just as like one basically the red background and the red font if this b8 is greater than one so that's how and also when we run the macro and we save it we're simply going to check if this is greater than one then we need to let the user know that there's a conflict and to let them fix that okay great so we understand that staff conflicts we also want to know for drag and drop when i select on something i want to know if there's been a move right this is going to tell us if it's been a move and this will go to false true and then false and then back to true again okay so that's.
Going to let us know here i'll show you a little bit more about that also we have a left position when i select a shape i have a left position and i have a top position of that shape right here i need to know as we select on something is there a change to this left position or is there a change to this top position so as we move something i need to know that timer is going to run and if it's been moved if the left position or if the top position has been changed then we need to know the user has made a move so that's all we have to do and then also i want to know has an invoice been an invoice been created for this if so place that invoice right here so if you see not all of them.
For example this one this particular appointment appointment id10 no invoice has been created so therefore the invoice has been row if an invoices has been created when i select on this icon i want to go directly to that invoice right which is right here invoice number one if it has not been created i want to create a brand new invoice so either we're going to go to an existing invoice or we're going to go to a new invoice and it's all going to be based on this right here that's how we.
Know and all we're going to do in this formula is simply we're going to imagine invoice appointment id now that invoice appointment id if we go to this invoice list we have invoice id and then we have the appointment id that's associated i'll make sure this is updated we don't want to have different make sure that's set that correct i think it's correct there i want to make sure that we know the appointment id that's been associated with each one of these okay so we'll be going over that code i'll double check that they shouldn't all be ones all right so as we move through that what we want to do is we want to make sure that we know the difference between something that's been invoiced and not okay so that's that's the reason we have this that is it that's all for the admin.