Manager so let's get started with just that I have written down all of your suggestions everything you have commented in both Facebook and YouTube as well as private messages and I've got them right here so let's go over them real quickly so we know what we're gonna be focusing on Eric pest or I'm gonna get your names all messed up so just bear with me it's impossible to work scheduled to be linked to this sheet yes work schedule something that is common even Louis cadet a I would also like to.
See a schedule featured linked to the time clock Piero story sure yeah we're gonna put that it in this week now it's not gonna be functioning yep we're gonna put all the capabilities in there so that we can have that for the future so we're gonna be focused on the schedule because that is an important part a lot of you've mentioned it richard hansworth also mentioned I'm wondering how easy it would be for employees to request annual leave and capture the request decisions and track annual leave sickness special leave invents yes leave paid or unpaid is also something that.
We're going to be focused on in this as far as capturing that data and so we're gonna put in room for that so that we can use that later on for the payroll so that is employer it would be great to tie this up with a payslip generator okay we're gonna work on that that's gonna be focused in the reports once we generate the payroll we'll have a report station where we can generate payslips so yeah that's something that's going to be in the future so we'll work on that disciplinary records we may put an Events Manager in here so we can track.
Events per employee we will cross that bridge a little bit later on depending upon how many people want to see some sort of an Events Manager tied so that might be kind of helpful GDP our requirements we may add some users security on to this a few of you have mentioned that to me so we do have some user security I think that's going to be kind of important especially we're gonna have some private information like social security numbers Jules mentioned would you like to see tabs for employee absence and training yeah we're going to include those tabs we're going to be adding tabs into this.
Schedule yeah we're gonna include that mercy wing I'd love to see more key employee data capture such as position title company will include some of that today we're gonna add a little bit more as far as population specific fields of payroll that are only associated with a manager I will try to get that in we will we do have employee types of week include filters later on Michael maja Rica messed that name up I'm sure the tax revision we nice to use code from your attachments project attachments that's a good idea I like the idea of.
Having attachments for each employee we're gonna add some space for that in this week so because employees have additional sheets you know you may have worksheets may have PDFs may have filled out forms so having attachments attached to a specific employer is kind of a nice idea so let's go we're gonna go ahead and add that in as well at least that the space is for it and then we'll add in the functionality later because we do have a lot to cover I don't know how long the it's training this series the employee is going to take it really depends on your suggestions or as long as you have.
Interest in it so make sure you view the entire video that helps me know that you're interested in this and you want to see this application to completion we can do a lot as long as you remain interested in in absorbing it and of course sharing and liking this video really helps me understand and know that this is something that you want to see Chuck Hamby I'd mention you can also do a performance review tab and I think that goes into the events and I think having an Events Manager would be good as mentioned before Kenneth mentioned I.
Have a proposal regarding working hours and Sweden okay I understand that I want to know the working hours how many hours in a week how many hours in a day so we're gonna include some of that scheduling as far as totaling do I understand the requirements for that a lot of you regardless of the country you're from you have certain specifications as far as how many hours you can work in a day in a week or multiple days so we're gonna put some of that in Frank these always appreciate Frank's comments the following groups are years of service male/female care we're gonna include gender in this in.
Today's yearly sit-down that's again a we're gonna go with that in events I think in having an Events Manager so that you can put any type of event like a yearly sit-down or discussion or something like that and to what occurred in that event would be really nice because there's so many different types of events that can be associated with employees so I think that's good a dashboard for department overview we will probably include it the dashboard a little bit later I'm drilled down as a nice idea when it comes to payroll and.
Posts Related:
Work hours of employees so we'll try to include some drill down features in this especially in the reporting once we have
All the data then we want to be able to drill down into it so I think the reporting issue will give us our drill down availability perhaps a period to drill down hours work details yeah that's good Webster webs print out the report payslip in PDF format that can be included also in the reporting and that's going to be at the very end once we have all of our data incorporated then we can focus on the reporting of.That and putting it in different format so that will come in in the weeks to come near and in this tutorial would you like to be catered to those HR or current data yeah for sure you know this is gonna be for HR and then we're gonna data mapping data mapping on imports nice so if you have hours or or employees or your data information is in a different type of format could we import that data maybe we can take that into consideration to bring in that data into the hour so although I would like to have an application that captures all the data itself you know as far as hours.
Are concerned we can build that out but importing it would be really helpful because a lot of you track those hours in different applications so that might be something that's a really good idea so thank you for that Satan was just puro Heat can we add a keyboard shortcuts that's a nice idea I like that keyboard shortcuts on tab maybe next week we're gonna tackle that and that's moving from tab to tab using a keyboard shortcut so I do like that idea Charles Charles is always a really a great big fan of ours would it be possible for you.
To demonstrate what changes would be necessary to scale this application so it can handle from 100 to 1,000 yeah definitely we're this application should be capable of handling thousands of employees so I think that's a great idea and I'll try to we'll try to build it and make sure it's scalable so they can handle a lot of employees and we'll go over some of that and how we're gonna do that today when we add in the employee tables I was thinking that you could use the list box for employee lookup yeah we're gonna have a we're gonna work on an autocomplete so that when you start.
Typing in the employee name the employee name comes automatically we got a lot to cover today kay 102 stone could you add time off request vacation time sick leave yes we're gonna add that in today and all your going to add security levels I think so I think that makes sense if you found we do have a year user security in details video way back almost a year ago some of you're familiar with that and I think having that type of security in an application like this is pretty important so I think towards the end we're gonna add in user login and per sheet security I think.
That's a really good idea to bring that type of security into a payroll especially something as sensitive a payroll you can't just have any employees looking up payroll records so it's a very sensitive data so we want to protect it Hashim Khan I suppose I have a manpower 5,000 place they'll need vacation so we're gonna focus on vacation exit resignation or terminate that would be four events so I think we're gonna include Events Manager in this and then Randy Austin I don't know who this guy is some crazy guy make this application shareable these.
Are my ideas yes share a bowl I'm gonna try to build this application so that the front and in the back end are always separate because having multi-user ability with this application would be really great we want multiple people to be able to make changes nearly at the same time with this application to do that we'll use a shared workbook but we don't like excels version or shared workbook we want to create our own so that we can share with anybody in the world using simply any kind of a shared photo like Dropbox Google Microsoft.
Onedrive or P cloud or so many other great types of shared so those are the suggestions keep those ideas coming I really love them and they really gonna help us form this application so let's get started into the application and I've got some ideas and some changes that we want to make and first of all we're gonna add some more tabs to this because based on your ideas we want to do that so let's add one more called scheduling do we know we need to focus on scheduling and perhaps another one called leave a lot of you mentioned.
Leave absences so we're gonna keep it very general called leave we can copy this formatting here that we have in the other tabs over to the new tabs and then we'll paste that formatting let me can go ahead and go into conditional formatting and just update that to make sure it's all set right into the manage rules we don't need two of them we just need one of them so let's update this one all the way until jj4 which is what.
We want everything else is fine and we can delete this one now we have one
Conditional formatting rule from efore to j4 and that constitutes all of our tabs so we can check that out and then close it move over all the to the selected column here v if we change this to seven but still it goes over to payroll history and then our new tab be nine and ten so let's look at that that looks right and then J leave so that looks right also okay so we have.The conditional formatting setup now we'll update the VBA code so we can get those now we'll notice we've ended it 1:45 so we need to update these to include everything from 145 to 164 on the scheduling and then for the leave we're gonna go from 165 all the way to 184 on that so we need to include those in that let's go ahead and go into the.
Developers and the VBA and update our code accordingly if we just update the code into the sheet one we have each be four to h4 on the selection change we'll need to update that to j4 and that's not gonna be quite enough but we need to go into the tab macros and make adjustments on here as well and I'll show you why if we were just to click on the scheduling it's going to go to the earnings information because we add 20 so we need.
To make some considerations for that in other words we've added 20 here we add 20 here we had 20 here payroll detail includes a lot so we so anything after that we have to compensate for that in other words scheduling it's gonna really start on 1:45 so we need to update the code for that we can do that in our Excel macro here under the tabs macro here so we can make a consideration for that here and in this code we want to.
Say add some additional first of all we need to change this to 184 because we're gonna increase the number of rows so we want them all hidden also we need to make a consideration for the selected column if the selected column is greater than 8 right because our new ones are 9 and 10 greater than 8 then the selected column equals selected column plus 3 what that's going to do is in our formula here it's gonna add additional rows on to that it's gonna add.
Additional rows let's take a look at that and see how that works so because we have to add additional three because we've used a total of sixty rows or eighty rows in our payroll so additional 60 additional 60 a total of 80 for our payroll so we need to add for that so when we go now when we go click on scheduling let's fix that that should be selected SEL and when we add that on to that let's take a look at it now so now we've got one 45 that looks right.
One forty-five and then when we got leave one sixty five perfect so that's what we want so that we can now update the formats now we can work with the scheduling so from 145 to 164 that is our range right here we can format those cells we can let's raise it up format those cells and add the borders to that we're not going to add the under border we're gonna add that on 185 because that's the last row so let's do that now 185 is here a lot.
Of top border to that I'll raise it up so you can see it format those cells and we're gonna add a top border that's going to be our default blue which is here and a medium thickness which is here so we're adding that top border because this 185 is always going to be seen so that's important now we can do the same thing and leave let me just check that scheduling we need to add out we need to remove the bottom border from 145 145 was our previous bottom so we need to remove that format those cells.
That was our previous bottom so we need to remove that because it's no longer our last row now 185 is there we go so now 185 is also on leaf we need to add so now we've got 185 it's looking good we just need to add the borders on the leave and that's gonna give us our full tab effect then we can start filling them out and format those cells and then we'll have the right and left border okay so now we've got now we've very simply we have our full tab area.
General info time clock history payroll history of scheduling scheduling and leave now we've got everything set now we just have to fill in the details but let's fill in let's start with scheduling on that I want to add a weekly schedule so let's call a weekly schedule and perhaps weekday is going to be our first column work Dan you know if it's a workday or not if they're offers workday what the start time is what the end time of that day is and perhaps the.
Total break hour so let's put break hours and then the total hours this will be a formula okay and first day of Monday start off the Monday and then we'll continue down all the way till Sunday on that and then I'll write justify them alright that we go and we can do a workday what I want to do is I want to I want to know if it's an off or if it's a work day I want to create a data on that so let's go ahead and I put.
In a data validation just gonna have two options on the list is it gonna be offer is gonna be work day work day or off separated by a comma and now we have the options perfect so let's go mark these as work days and then we'll do Saturday and Sundays off just just to put some data in there so we can see where we're going on that I'm going to conditional format those and for start time and end time I want to create a drop-down list of times so that you just have some control over.
That why don't we go down start at 185 I've get to create a list here of times just want to start it here so that's always a visible and something out of the way starting out at 12 o'clock a.m. I want to include all times because you never know when shift start and I want to increment it every 15 minutes now how do I get 15 minutes I want to make sure I'm going to use a formula so we know a day is one one so equals one divided by 24 it's gonna be one hour but what is it if it's 15 minutes well that's divided by 4 again right so divided by 4 again and.
That's going to equal one zero zero four zero it's point zero so that's our 15-minute increment copy that I'm just going to paste the value because I'm only concerned about the value and now what I want to do is gonna copy that control C equals 12 a.m. plus that number good that's what I want now I can continue down that and you see how we got increments so now we're going to go all the way down for a full day's schedule and let's continue down all the.
Way we're gonna go all the way to 12 and midnight so that'll be our list 5:30 a.m. too far okay here we go 12 so the last time will be 11:45 p.m. so that's our list that's our x list let's capture that list and we will call that just times so now we've got our time set so now we can use that in our drop-down list and it's going to cover all the all of them so its start and end times here we can use that as our data validation and bring that in and I'll be at a list.
And it's getting equal times all right there we go now we've got our now we just have to format them accordingly so let's go into home and I'm we can format them as time but I don't really want the seconds on here so let's modify that and just go to say this you can you can use whatever times you know based on your current I'll just use this at 12:45 a.m. and then we'll probably sin or left justified other ways good so now we've.
Got our x in there we'll format this table it's merge and center this and we'll use our darker fade for this theme which is the blues filling we're going to go from medium to a little bit lighter so the fill effects will create a fill effects we have two different fill effects ones for our header this one is going to be used from dark and then we'll the one below that will create just one lighter on this one will.
Center it format those cells and we'll go ahead and give this a fill effect of just a little bit lighter here so it'll go to our medium and then to our light blue that'll give us a nice fade out effect for both of them we can give it a border inside border of the lines so use those thinner lines for the inside and also we can use a top border so that gives us a.
Nice fade effect and I want to create this schedule and we're gonna use this for each and play will focus on the data aspect of it a little bit later on right now we're just creating the information we don't want to use connected to a data just quite yet because we want to make sure that we have everything set just the way we want it all right let's create some borders and I'll keep this blue and this is not gonna be user entry so we'll keep this darker because the wider we want we want user entry white.
Or so these fields are going to be for user entry these fields are going to be for user entry let's color those white and then give it a given a alternating color as well so that's a little bit easier to see using conditional formatting or there's a few ways there's only a few cells using the mod which we've done before and then we'll use our and then that'll fill out right remember this comma could be semicolon depending upon your different conditions fill effects and let's go into fill effects.
Because I'm going to use a blue that I've used before which is here and that's common and that we have recent colors when we use fill effects even though we're not using two colors we're using the same both the top and bottom but our color we can easily locate our recent colors when we use fade effects okay that's good and give it some borders and then we'll be done with this and we can move on from that I'll scroll up so you can see it format cells and then we will give it a lower border and then.
Dotted line for the center then maybe a line him it's continued dotted line okay so we're good with that we've got that formatted alrighty we're gonna keep this dark cuz that's gonna be a formula and we're gonna keep this and let's go ahead and add that formula now just so you can see we're gonna finish just do a 6 o'clock p.m. 9:00 a.m. and then we'll copy that down just a standard schedule for those days 9:00 a.m. to 6:00 p.m. with a one-hour break that's going to be 8 hours total so in this case it's simple math equals and then we'll have.
To multiply times 24 equals and time minus the start time and then we also want to subtract the break too but the break is in one hour you see the break is one hour and we have this equals so if we were just to do this right we'll get to 9:00 a.m. that's not really what we want we want a number so let's go to number right but 0.38 is not what we want either we need to multiply this times 24 because we need to know by the.
Hour so it's gonna be nine hours and of course we have to subtract the break now so minus the break hours and that will give us our total hours which is 8 hours and we can copy down that formula we'll just that and bring just the form as I don't want to mess with the conditional formatting and there we go so now when we add in the times we can copy down this on our work days and paste those values in there and then also the break so any any differentiation in time is going to result in our different time so that'll.
Be really really helpful let's Center this as well we can Center we can Center both of these ok so now we've got our total hours so if we just make a slight change to 6:15 change it to 8 and 1/4 this brings our hours once again we need to multiply this times 24 because in Excel one equals one day so we have to remember that so if break hours so we have to differentiate between that I just want to add some conditional formatting here for the off.
Days so let's highlight these and then we'll add some conditional formatting numeral we'll use a formula and we're going to start with the first possibility it's also the first row of our conditional format so the first row of the possible change it's also the first row of it's going to apply to you which is f4 now we also have to remove this dollar sign we do not want that fixed we need to use that for all the.
Fected rows equals off in parentheses if this is often I want to format this and what I want to format it let's just make it a gray fill it in with a little bit gray a light gray and then we'll use the font of a little bit darker gray so that way our off dates are clearly differentiated between our on days and now we see if we were to change the dates off it'll work okay great so that's working just fine we've got our schedule set up let's add some totals in there total work schedule just so we.
Know the total hours total breaks maybe and networking time which means is our actual working time and let's go ahead and add some colons in that so it's uniform and then we'll write justify it color that a little bit differently and give it a darker color because it's a different type of total give it art and format this I'll scroll up format that for you overall alright there we go.
And put some lines and our dotted line as our vertical now we just have to add those toes let me raise this up so you have a little more viewable area alright now we can add in our totals our total work schedule would simply be the sum of all the total hours say plus the total breaks here sum of our total breaks and we give arts our total hours which is 45 and then of course we're gonna subtract the total break equals and first we're gonna sum.