Us because we're gonna show you how to do that and create this if of course you have not subscribed yet make sure you do that click the button down below subscribe and also click the notification icon now that is gonna make sure that you get alerted to when we have new trainings each and every Tuesday so I'm really great we go through these training each and every week I bring you something new so you're shares your likes your comments I always appreciate it I respond to each and every comment I always appreciate your comments even if it's some criticism or some feedback I preached everything so thanks so much why do we do this why do I do this why do I create these amazing applications for you it's more than just teaching you excel I want.
To teach you how to become financially successful with Excel and one of the reasons I've done that is to create the mentorship program and if you listen to these videos before you've heard me talk about the mentorship program but I've never gotten a chance to show you what can happen when you go through the mentorship program or what success you can have and today I want to show you just that many years ago in 2016 I created a relatively basic software called labor burden calculator and it's basically this it was nothing fantastic but it served a really good purpose the purpose of this was to allow companies to enter employee information and then figure out the.
Actual hourly cost of their employees based on whether it's uh employee benefits paid leave and all sorts of insurance and rates and allowed them to figure out their hourly cost and they could enter multiple employees and then just select an employee nothing amazing nothing incredible but it was certainly useful so what it is I created this application and I put it on the market and what I'm saying on the market I just created a website and here's the website that I created it's called the labor burden calculator it's basically just a one-page so there's a video explaining the aspects of the video you can watch it in labor burden and so it kind of goes over the features of this all the application and then down at the bottom just got some reviews and things like that and how to.
Just something basic so this is what I want to teach you and of course some bonuses and things and then we've got some different prices you know County version 277 so what does this do let me show you let me take a look through my Outlook email and take a look at the payments and you see here if we scroll actually all the way up starting with today actually and then all the way down so each one of these is payments for that single application PayPal after PayPal after PayPal after PayPal fully automated fully passive income and that's exactly what I want you to get you on I.
Cover so what I want to do I want to create a task manager doing basically what I want to have is a view calendar so to do that lets just start a heading and call this task manager as we always do and then again I'm gonna save these first two columns as for admin so we're gonna call those gray and you'll notice that I use different colors sometimes I try to keep it different so let's just take a look at the colors that I'm using I'm gonna drop this down while we're formatting it and if we go into the page layout and we see colors I'm using in this one what we're calling a flow flow okay for this one I'm gonna use flow so if you want to match that you can see those colors in flow because when you get it you might see a different theme different color so okay let's take a look and let's merge the set of these we'll go all the way to K and then I want to create it's just a larger title go in the middle and then maybe 30 something a little bit bigger.
And then I'll change the font something a little bit different that's not good that'll work okay and so let's just change the color here and I won't put a little fade down here this screen is not gonna be very exciting it's gonna be really basic and that's good we don't want something but the purpose is gonna be really amazing so let's give it a fill effects it means we don't have to put too much effort into the screen itself probably something fill effects and we'll just use a two color combination maybe this one and this one and then again a little bit faded on the lower row the second button rows gonna be used for shapes so we'll have a few different buttons we'll.
Have next week in previous week and I'll also have the ability to add a new schedule or and add a new task so we want to be able to make sure we can do that okay so now this is gonna be basically it and I'm gonna color first two rows here gonna bring those down and then maybe I want to have a day of the week so this is going to be let's say Monday Tuesday Wednesday Thursday Friday Saturday Sunday okay and I won't call it these so I've got the seven days little color a little bit differently and these will just give it the standard background color of blue okay that gives us a nice.
Fade and Brawl stand it over a little bit here all right so we have the basic format of what we're going to be doing and now we're gonna include some icons and I'll show you first day let's go I want to put the first date here in this section I'd like to have something like a drag and drop so I'm gonna merge in the center this and I want to have the place where people can drag tasks in which they don't want to schedule and once they want to unschedule so drag here to unscheduled yeah.
Like that I'd like to have that and let's uh wrap that text and we'll make it bigger and I want to make it different very different I want to make it look I wanna make that stand out so you just know they can drag their tasks over into this section and that way they can automatically unschedule them it's gonna be really unique something very interesting and will give it this double line board around so I wanna make that area stand out that's what I wanted to have drag to stand out and then this area I'm gonna put the schedule time we want to schedule times here because we're gonna have the date so scheduled let's just put the time here and then I'm gonna merge and center that down.
Posts Related:
So that's gonna be and then I'll put the times down here down here okay so we'll make that full and then I want the days here so I'd like that the days of the week like Monday Tuesday Wednesday but
I do want them based on a date in case you want to change it so let's do a date here on what the first let's call it December I want a Monday I think December 31st of last year so 12 actually 30 3201 9 that is a Monday I believe and I'm gonna format that so I'm gonna put the day up here so I'm gonna put this is equal to this it's gonna be real and then what we'll do is we're gonna format those so this is gonna be I'm gonna put the day Monday Tuesday Wednesday on here plus one okay and.I'm gonna bring that over here like that and then I'm gonna do the same thing here equals this plus one but I'm gonna format them differently plus one so what I'm going to do is basically each day of the week here so these I'm gonna format as like the month the day so let's go ahead and format those and then I want the day number it's kind of a nice way to show that so what do I want here I want something like basically like what I want this but I don't want to show the date so I'm gonna take get out of that and I just want to show the month the day and the year so get rid of.
That and click okay that's what I want to show and I'll Center that and then I want to show the day here so in this case again Center that and this I'm gonna format and to show the day we just need to use for DS so we'll go into the custom and we can type out DD DD okay that's what I want to have Monday so that's what I'd like to have here that we're gonna give it the same kind of format here basically the same fate as we did above so we'll fill effects formatting basically as we did just a little bit of a fade like we did with this using the same color scheme that we did and I'll bring this down to this so we get a nice color here and then of course we're gonna do the same thing here I'm just gonna create this then I'm gonna put some borders around it so I can just fill it with this color yeah that looks nice that's kind of what I'm looking for and put some borders around here great.
We'll just put some same color that we're gonna use the same corner that we'll use them which is this dark color here and then we'll use it all the way around here and then through here okay good now that's what I like to hear but we don't need actually this border here I don't need the middle one okay that's good enough right there okay so that gives us an idea of exactly the the fade that I'm looking for and I'll use control-v bold those so I'm gonna have the days here and the times here but what are the times now I've started just a little bit to help us move along and I've given it some starting time so let's just show you what I have here for the admin and settings this I want.
To start time and I want the user to be able to choose the start time to from a drop down list and we can have a drop down list which we can create but I'll put that drop down most will be something called times and we're gonna make that so that'll that'll be here on the scheduled time so what is our start time and we have intervals I want different intervals so we can choose the intervals we have tasks categories we have colors we're gonna be able to choose the priority which is nice a status pending scheduled and then intervals is really nice so I'm gonna go through every step of this but gives you an idea of what we can choose from and so let's just go back and let's make this.
Equals and I've created some named ranges let's go over that so we know where we're starting from just to help things because these videos are long enough as it is so let's take a look at that I've created something called duration and if we set that that's gonna make all the duration up to the duration so you can have those directions of your tasks up to eight hours of the duration you can create more we have interval what is the interval the intervals the interval is that decimal based on a time and I'll show you how we get to that in just a moment we have a priority of course that's the drop-down list that's located right here when we tab over we see that's the priority we have a.
Start time a start time is gonna be based here we have statuses statuses are going to be based on right here and then we have a task ID task number of tasks tussles go over those with you in just a bit so let's start out with the tenth let's see how do we get this interval now let's take a look at what one day is one day it means one in Excel one day it's considered the number one okay so what would one hour be one divided by 24 so one hour in decimal form is point zero four one one.
Six okay great so what is a half an hour well if a half an hour would be divided by two again so we had a half an hour would be point two zero so if you keep doing those decimals you're gonna get this and you this is how we get our five-minute or ten minute so all we do for example five minutes is one day divided by 24 divided by 12 10 minutes divided by 6 and so you see how we get these decimals now it's really important because if I'm gonna increment on our x I need to know what this.
Interval is and so what I did is I see us from us drop down this five minutes 10 minutes right so if we have which is this list right here so what I want to do is I want to figure out the interval based on that selection so if they select 10 right I want this interval point 0 0 6 9 i forward to be based on this because that's gonna help us get our intervals for our time so let me show you how that would work so all we need to do is this has been labeled start time so in our schedule I'm gonna change this I'm gonna call this equal start ok great so now we have point 0 3 3 which is what we want of course we want to format it in time so let's do that let's go down to let's just say D.
100 deep D 100 and then I'm what I'm gonna do is I'm gonna format every single cell between D 100
And all the way up I want those all formatted on time so I'm gonna hold down the shift and then I'm gonna format those so I'm gonna go into the home and then custom and I want time but not everything because I'll include seconds so let me just update that to the time that I'm looking for I'm gonna you choose this time 1:30 p.m. you can choose whatever format you want so you see now when we go back and we see it's formatted to 8:00 a.m. and that's what I want here I've got this in 85 to Z let me zoom it up a little bit so you can see it's a little too small okay so now we have.That we understand that so 8 so now what I want to do what's the next increment do I want 8:05 8:10 8:15 will that all depends on the increment right it all depends on what this interval is because if they choose 15 minutes this is going to change so what we do is we call this interval I've given this a name called interval so all we have to do is go into our schedule and say equals this 8 a.m. plus the interval interval right right here that's the one we want that's all we need that's how we get a 15 so now I need to all I need to do is pretty much drag that down drop it right down but.
That's only part of it now here's what I want to do basically if we drag that all the way down I'll show you one of the issues and how we're gonna fix it let's bring it down to 96 or what have we here that's fine it looks good not at all it brings it down all the way to full day I really don't want to bleed into the next day and I don't want it all so depleted especially more than 24 hours so if I change this to like 30 minutes let me go back in the scale you see now it's all the way to 5:30 a.m. the next day and I really don't want that it goes from 8:30 a.m. and so if I change this back.
To let's say even 1 hour you see it's gonna bleed into multiple days now and so that's gonna create real problems for us so we really don't want that so how do we avoid that well let me show you how all we need to do is pretty much add in a formula here that's gonna help prevent us so the first thing I'll do is I'll copy this and now what I'm going to say is if and then d5 Plus the interval is greater than one which means a full day then what I wanted to just show empty otherwise show the d5 close interval okay so let's see what that now that's not complete yeah it's still gonna create us a problem but I'll show you how to fix that so when I double-click and bring that.
All the way down that creates a space when it's after the day but then we get this value here for anything else so to fix that all we need to do is check the cell below so again let's run another if statement if the cell above I should say so above is is blank is equal to blank then show a blank so then blank cell comma blank otherwise continue on with our formula and the parentheses okay now let's drop this down and see how that looks okay that's blank that's gonna show all the way down to 96 that's what I want the blank cell for 30 per fect that's what I want alright let's finish up this and this formatting here so I'm gonna go all the way down here and then I'm gonna format huh just go all the way to 100 or so and then I want to just give it a little bit of a format so.
I'm gonna format this cell so I'll use the double line on the bottom I'll use this darker color here and the double line on the left a single on the top left and bottom and in the middle I'll use a dotted line and that's gonna give us a nice look and feel that's what I want okay so what else do we need me some icons and pictures in there so let's add those I'm gonna insert some pictures and basically I want to add all of these and this one but not not this one I'll show you those save and cancel buttons how we use those later so pretty much all of these and all let's set it to a specific height like point two and that's fine there except for one of them I'm gonna make.
It a little bit larger that's gonna be our icon for the application we're gonna use that that's this one right here so I'm gonna make this one a little bit larger so now that we have that we can add some more buttons I want to have previous and next week button so that you could usually can easily select between those so let's put a dot here and then we'll call this next week I'm gonna give it to a look let's just say next week and then bring it out a little bit and something a little bit nicer so that they can choose and I'm gonna format this because I don't want the spacing there and we'll duplicate it for that so we'll go into the text box here and I don't need point zero two on the left and then I don't need any top or bottom okay so we've got that and now let's just.
Bring it down here a little bit and then we can reduce it that way we can reduce the size and unlock consent of that here all the way okay I like the looks of that and go ahead and put some shape effects in I'll use this here and that's good for the next week and then I'll duplicate that using control D and I'll put in previous week and then all I'm going to do is rotate that and then I'm gonna flip it horizontally okay so we have previous the next week align them in the center and so those are gonna help us navigate so we can put that there okay so what other buttons do we need I just need really two buttons I need to be able to update a task and I need to be able to add a task so let's insert two buttons here and we'll create those buttons so they're gonna.
Put them right up here the first thing we'll call this update task and then I'll write justify that and Center it so that it looks nice and then again make it give it the same look and feel as the buttons we have before and which is this one and then I'm gonna use this icon here this one here and then I'll bring that to the front here okay and I'll do the same for the rest of these I'll bring these all to the front that's what we'll duplicate this button first before we do that and then we're gonna bring this one and then I'll basically want to add a task so have a button.
That's gonna add the task so we can call this add task let's get out of this and we'll call this add task just two buttons could be quite simple add tasks okay so perfect and that's all we need for that I'll make this button a little bit shorter I think point eight should be fine for that and then just an icon for this something simple now we have adds hats and again we always want to group and totally control so I'm gonna line it then I'm going to group it remember this is a quick.
Access toolbar here you can click more commands if you want to create any yourself just select all commands and then you can drag anything over here you can add anything over you want to create your own okay so we have add a task and we have update task again let's enter that and group it and okay so we have update task and add test those are the buttons that we're going to use for now and those are fine I'm gonna make these a little bit bigger a little bit on the on the height point two eight just so they stand out a little bit more because we're gonna use them frequently we have all the days we have a scheduled time let's format this one so that it looks the same as what we have everything else and we believe we use this color we use the fill effects of this color and then.
This color okay so that's the same codes that we used on the other so everything is nice it looks the same as everything else nice okay so what else do we need to do well what I want to do is I want to have basically I want to create shapes and those shapes are going to make up my tasks so how do we do that well the first thing we want to do is we need to keep track what shape we're on so that's important so I'm gonna put that something in here called selected tasks I want to know what the task is so the name of that task is gonna go here and we have our tasks I've got some data here and this is gonna keep track of our tasks so we have a task ID which is going to include Ted the word task and then a number we have a task number that's gonna be unique we're gonna.
Show you how to combine those task name a name a description a category that we're gonna be able to sign a priority a scheduled date scheduled time a duration and then the status so that's gonna help us so basically anything with a pending status I want to put in this here everything that's pending I want to go here and I just want to have it the same height as the row and then I want to drag it into here and then have it expand based on the duration so for example if it's one hour and then I'm going to have it basically have four different if we're at what are we 30 now let's go let's go to 15 I like 15 better for our purposes okay so but either way both will work just fine.
So I want to have that here so we also need to know the task row and I'll put that here that's the row of on our database in sheet 3 task row so we need to know that I also want to know the next task number the next task how do we know the next task that's really it all I need to know the next task can be based on this based on this so I've created some named range remember we said there was three more name ranges that we didn't go into so let's go into them now those are task ID if we tab over here we see that's the task ID that's a dynamic game-droid using offset something we've been over many times before if you haven't seen my videos just using offset it's a great way all.
Right next up task number again using offsets so that means that's gonna grow and of course we have task status task status is all the way over here okay it's a last row so we have those named ranges that's gonna help us out so the first thing I want to do is I want to get the next task number in this case it's 119 119 how do I do that okay so let's take a look at this the next task number equals it's gonna be we're gonna use the max formula but I always want to use if air just in case it's an air if air max what is their max task number actually task number so that's the max.
But I don't just want the max I want the next one so plus one is gonna give us the next one but what if there's an error there's an error if there's no data right so if there's no data why don't we set the task number initial which you put is 100 okay so now we have that now we have our complete so now the next task is 119 and that's just what I want and it's if for some reason we create one and we put in 120 here the next asset is automatically gonna go to it's based on this number so I have to fill in a text on this too as well it's based on 121 ok so why is that why didn't it change why.
Didn't it change because our counting is based on this row here our count is based on this row our columns here but our count is based on the number of rows with text in call them here so as soon as I add a text here it's gonna change to 1:21 so that's how we use it again now there's a lot of you questions you've asked me well my ID numbers combine the numbers and letters how do I get the max well this is how you do it you take one column with only unique numbers and then you combine them using VBA or using formulas you can combine them that's how you'll do it okay so we have that let's call these a little bit differently so that they stand out we know that's all we really have to.
Do it's quite simple for our purposes here so I just need the task row and the selected task task row is going to be also a formula what is that well that's going to be using a match all we need is a match so what do i I want to know I want to look for task ID and I want to know what row is it in so let's take a look at that if we use match let's say our selected task here let's just put in 1 in for now it's called task 101 task task 101 we know that task 101 is located on row force where he needs a match fit equals match what are we matching we're gonna look up select a task and what's the array the array is task ID 0 we're on an exact match and then + 3 y + 2 because the.
First ones on little one like the first one is on row the first one is on row 4 here so we want to add 3 right if we just return though if we don't add 3 it's just gonna return 1 we don't want one let's put that to left this close a little bit too dark for us to see it clearly come on just a light color these colors are new to me so all that that was not seized something like okay so that's better so that's all we need that's all we need is a really easy admin on this one so we have the selected test now the ideas as soon as I select the task of shape I want that name to appear right here B so the best way to do that is to create a shape I want to create a sample shape so let's do.
That it can be really anything it doesn't matter just I mean it's a square shape and I'm just gonna call it here something like that I don't need to know anything else we'll actually we'll take the outline now I just want to put a light a liner let's just do a small outline here okay I'm just gonna call this so I'm gonna give you a shape a call I'm gonna call it sample because this is what we're gonna do so basically what I want to do the idea is this I want to duplicate it using VBA control D but I want to give this one a name and then I want to give it a size based on this what is the name of this it's gonna be whatever is task 118 task 119 so it's gonna be based on that it's gonna be the name is going to be based on this ID that's how we can tell each appointment.
Differently or each task differently we assign it a name we'll do all that with VBA what else do I want to do well let's just say I have a task here let's just create a sample task right let's say I have a task here and well let's just call this leading with bomb okay and so let's say we now this meeting Bob now if I click on this what I want to do is I want to put that name and I want to put it right here in b2 I want to know what task we've done it and I can do that with VBA but what else do I want I want to be able to edit this now how would it mean you edit it I want to be able to change things I want to be able to change the name the description or or create a new one so.
We can do that with a pop-up form some people say I don't use usual forms too much I don't you're right but in this instance it works perfectly now to save time I have created a user form for this because I create user forms extremely slowly so this video would be 3 hours so I've done just that I created a user form already and because it's a great way to save time we've done user forms so but I'll walk you through every step of this user form so no worries on that so basically what I want to do is I want to have this user form pop-up if it's an existing task all the information is gonna appear cast name the task status all the information from the table that we have created.
Here is going to appear and so basically all we need to know is the row of the task all we need to know is the rung of course the rows gonna be right here so once we know the row I can take all the information bro for and bring it into that pop-up form so we're gonna do that but how do I know it so how do I bring it up so the first thing we want to do is I want to create a button an edit button and I want to bring put a really close to this whichever one we select and I want to have that edit button like right about here and they don't wanna have the delete button like right about here so that means as soon as we select it the edit button pops up we click the edit button in that form pops up so let's do just that in fact I'm gonna duplicate this one I'm gonna create a button.
Out of it but something a little bit different so all in fact I'll just do format I'll just give it a different color something like and so given a size I wanted a little bit bigger than this so let's put to push this and back and see how what size we want I'm gonna bring a sender to the back and a little bit smaller so we'll go something like point two five and then point two five make sure it's square we can do the same thing with that okay that's kind of nice that gives us a nice so I'm gonna take these two shapes and I'm gonna send to them both then I'm going to group them and then I'm just gonna call this edit button and then I'm gonna do the same thing in fact I should.
Have duplicated I'm gonna control C control V and then one okay so now I've copy they don't want the same one for this but I bring this to the front format and bring to the front and then we're gonna do the same exact thing here and then just give it a name so I'm good again I'm gonna use my selection it's easier and I'm going to select both of those sent to them vertically and horizontally group them give it a name so we're gonna call this the delete but I also want to be able to delete an appointment okay nice so now I've got both of these but basically what I want to do is I want to bring both of them up at the same time so let's zoom in here and what I want to is on for both.
Of these nice and close together and so they both appear at the same time as soon as we select on a shape so now that I've got them standard now I'm gonna hold down the control and I'm gonna group both of them together and I'm gonna call this edit group okay so basically when I select the task I want that edit group to appear to the right of the task no matter what task we've selected when I click on this I want the pop-up form to come up so I can edit the task very easily so that's just what we're going to do okay so you just I'm to get an idea and the good thing is what I want to do is I want to be able to drag this task here and have it automatically schedule it or drag it here and have it automatically scheduled so that's just what we're gonna do let's format this a little bit better than we already have I want to give it a little bit better look all we need to do is format.
The columns and the rows here we'll go all the way down to something like here and then format those and then put it aboard I'll just put a border all the way around and then just day probably a little bit lighter of it and then maybe a dotted line in this Center okay there we go and the only other thing that I want to do is I don't want to have it perfectly solid I want a little bit of a transparency so kind of a nice in fact what I'm gonna do is automate this one our sample because that's what we're gonna be creating from I'm gonna give it a fill and I'm gonna give it just a not a hundred percent fill so I'm gonna go into more fill colors and I'm gonna change this to 15 that.
Way it's a little bit transparent a little bit and the reason we're doing that is because I want to be able to see those lines underneath a little bit it's kind of nice right okay so now the colors are going to be completely dynamic that's gonna change through VBA what are the colors of these I'm gonna be based on whatever color you set here so if we set a meeting color blue it's gonna show up blue if we sent a project regions personal whatever colors we set here are automatically gonna change we've got three greens we don't really need that okay so we can change that let's go a little bit darker color so that we can use okay I like that alright so basically the colors can be completely dynamic too so that's why it's gonna be really great so what do we have so we have these so as we drag it we can schedule all I want to do is drag it now there's no event in Excel that that's.
Gonna run as soon as I move this no in no event so what I can do is I can move it and then it's click update task and a little update it right away so let's do that let me show you just how we're gonna do that so again here's what I want to do I want to duplicate this shape for every new task I want to duplicate this and then create a task based on that so for example let's just show you what we might wonder let's call this task 101 cake says we have a task already associated with that so when I click here I will the first thing I want to do is I want that name show up right and b2 so how do we do that okay let's go into the VBA and just see how we start with that.