Collaborate on Payroll Receipt Template for Administration with Ease Using airSlate SignNow

Payroll receipt template for Administration

what if you could create a complete payroll system in Excel complete with both unlimited payrolls unlimited employees salary and hourly employees deductions additions pay time off and a whole lot more hi this is Randy with Excel for Freelancers and today we're gonna do just that when we create the ultimate employee payroll manager it's gonna have everything a payroll needs and a ton more and best of all we're going to design it completely from scratch every feature every function every formula right here right now I cannot wait so let's get started thank you all right thank you so much for joining me this week I've got a really fantastic training I am super excited about payroll payroll can be confusing it can be difficult but today we're gonna break it all down and I'm going to show you just how easy it is to create your own employee payroll manager this is the sample we'll be doing most of the design from scratch so I'm going to walk you through every step before we get to that just a few things don't forget to subscribe to this channel go ahead and click that subscription and of course the notification icon Bell that will ensure that you get notified when I create these trainings each and every Tuesday this template is absolutely free all you need to do is click the links down in the description of course if you do want to support this channel there are so many great ways to do that one of the ways is to grab my 300 template pack that is 300 of my best templates that I've created just for you and put them all in a single pack and that means that you're going to be able to get my best the world's greatest templates of the last six years that I've been hard at work and I put all them into a single zip file and I also added an incredible library and that means you're gonna be able to single click on the workbook list to look it up and pull it up or a single click to open the video YouTube so it's a great list that Library lets you save a lot of time and you'll start your project you can use these you can resell them you can update them they're yours they're copyright free you can do whatever you want so I'll include the links down below that's my 300 amazing workbook pack okay great we're going to get started on this just to let you know if you do like these trainings right you want to have an idea you want me to update something I'm going to add a feature maybe you want me to fix something or you want me to focus on an area that I didn't do that I'm doing that for my YouTube and patreon members each and every week along with that I do go ahead and add in an additional PDF code book along with a ton of other features and that's on our patreon platform so I'll include that link down below this like any other week I'll make sure that you get this updated file and it's based on your suggestions so your comments your ideas your emails I answer each and every one of them every single day all right we're going to get started what we'll do is we'll go over a high level on this training and then we'll drill down and then what we'll do is we'll put this workbook away and I've got another one that we're going to be working on so but I wanted to give you a pretty much an idea of what we're going to be building before we actually build it okay so the first thing what we do is we have a list of payrolls here so any type of payroll that you want is going to be listed here now they're filtered by your number so if we were to change the year to 2022 I have not created any payrolls for that year so nothing will show up when we do select on a payroll we want that payroll information all the employees that are paid to show up here if an employee has been saved and paid right it's going to show up in that dark blue with check as we move through the employees if we decide we are going to pay some money we just select on them we make our changes so we can add any deduction item we want here simply adding that deduction we can add any addition item we want as well if we by doing that and then all we need to do is if there's salary it'll automatically pay obviously this guy's salary is not 135 so we can update that to about let's say 4 500 or maybe he was hourly hourly okay so what we can do is we can then save a next and automatically he's going to be paid so you see that automatically he's just got paid forty five thousand all the taxes are automatically calculated and you see that we now have our information our gross pay and everything like that so automatically saved and updated so really really a cool way to automatically update payroll we can add or edit any employees if we want to add an employee we can do that right here simply by selecting on it also what we can do is we can then also add a brand new employee if we want to add a new employee we can click on there so we have that benefit and also when I edit and play I think I need to make an update on this in the code I think I just it's just the row number notice that the wrong employee was showing up fix that okay great so we also want to make sure that we can add any personal time off if we decide we're going to add a personal time off we can do that and it's automatically going to be saved and moved on so that's a great way to handle that so that way anybody that's been paid is automatically we're going to be able to show the year-to-date information on both deductions on payroll additions payroll tax and payroll deductions personal time off if there was any and we're going to show also the addition so we have all that in one single screen we'll be able to do all of that okay so what do we have inside the admin screen I've got a little bit we're going to be able to browse we've got employee pictures so we need to know the folder that they're located we've got the overtime threshold meaning when does it reach over time anything over eight hours here is overtime a payroll frequency here we also have an employee type the list of employee types payroll statuses the list of years we saw that drop down list we have the federal local state taxes along with the percentage of that and if there's a limit in other words the there might be a limit on the total that an employee would pay for that we can set those limits right here we also have the pay deduction items and our pay addition items that we can have we can add to that list the different paid types that we have and also the frequencies and a frequency modifier here so we can a divisor here that we can add that in okay we also have a list of payrolls remember we created each individual I've created a total of six payrolls we have the from and to date gross amount and the net we also have the paid employees when employees paid is kept on a list those employees are paid it's tracked on here the individual items such as the tax item or deductions or additions or PTO is all tracked on this sheet right here we have the timesheet data so we can see we know all the time sheet that can be contained directly in this sheet as well as a list of employees so we have all of that and the whole lot more so I'm going to be sharing that with you and that's exactly what we're going to be creating so what I'm going to do now is I'm going to move this sheet over and then we're going to start with mostly a blank sheet but I do have a little bit of information I can do some conditional formatting in this sheet that's going to help us move things along a little bit quicker because this is a generally intermediate to Advanced type of training so that we can sometimes move a little quickly over certain types of formats because they're relatively easy okay so what I'm going to do these first two columns I'm going to use an admin so we're going to color those those columns will be hidden you'll notice that some of the formats are already here I'm going to add a title to this I'm going to call this employee payroll manager okay so we have the title there now this second row here I'm going to use this for our button set so this is going to remain blank except our buttons are going to be put there and our third row we're going to put some titles in here so here I want some headers for our ranges so we're going to put this select payroll this is the one where we're going to select which payroll we want next up are header here I also want to put in the employee details here excuse me payroll details payroll details will go here next up is the employee details when we select on an employee to pay we certainly want to know the information about that employee so we'll call that employee details then if it's an hourly summary our employee I want to make sure that we have an hourly summary here so we'll put that in here that section and then I also want the pay summary so for all employees we have a pay summary it's going to list those totals next up in this cell what I want to do is I want to put the employee tax deductions employee tax deductions we'll go here and then next up I also want to have other payroll deductions so other payroll deductions and then here I want to put in our personal time off so personal time off and it's also known as PTO so put that in here PTO okay lastly I want to put in the other payroll additions okay so we have the other payroll distance now let's put in some subheadings for some of these as they contain it so our tax deductions we want to tax item I want to know what items that so we saw the tax item and an amounts so we want to put the amount and then the Y2 date totals YTD or otherwise known as year to date so that's a summary for the given year and I also want the deduction item so here deduction item the amount of that will go here along with the YTD here here today next up the personal time off I want to put the date that's the payroll date that the PTO was taken how many days were taken here along with the amount now we're going to determine the amount based on the average daily cost of a specific employee I also want the addition item name so we'll put the Edition item here and next up I want the amount and the YTD here the year to date okay so those are our subheadings for our list so that looks good just the way we have it we'll be adding more to that of course the formats now let's go ahead and put in some subheadings down here I want to be able to select on a year so select and then year here there's a drop down list of data validation that's already here to help save us some time and we have a Year's next up I want to put in the payroll what is the payroll that's the header item and what is the status of that so I'll put that in here status okay next up what I want to have inside the payroll details I want to have the payroll date so payroll date here we'll put in that and I want to have next up the status of that what is the status of that payroll and then I'm going to skip a row and then what we'll have is the pay from date pay from date here and the pay to date so this is going to be a pay to date so this is the to and from dates of our payroll period so it's very important I also want to know as far as a calculator the total gross payroll for that given payroll and I'd like to know in the next one the total net payroll total net okay so so we're going to have that and that'll be calculated based on some formulas okay next up we're gonna have some employee information I want to know the employee name so we need to have that name here and then we'll skip a row and then we'll go down to payroll date what is the date of that payroll and then I want to know the hourly rate hourly rate of that now this is actually going to be dynamic I want this label to be dynamic based on the type of employee so if it's an hourly employee or if it is a salary employee I want to know the difference so we have some information here that's going to help us to do just that to determine that and so let's say it's hourly hourly here for now and I want the pay type so pay type is it hourly salary or something else it's going to go here next up I want the PTO taken how many days are taken inside that if any and then lastly I want the OT or overtime rate overtime rate okay we can probably put over time rate got we have enough space on that okay very good so now what I want is a little bit something Dynamic here and the reason is I if it's if it's a salary I want to put here salary if it's hourly rate I want to put salary so I need a formula to do just that so what we're going to do here is I'm going to use a formula here equals if then we just parentheses here P4 equals hourly then what I want I want to put an hour red hourly then I want to put an hourly rate otherwise I want to put in salary amount okay that looks good okay so basically as we change this to salary it's going to be put in salary amount or we could just put in amount or something or pay amount the reason is it may not be a salary maybe we want it could the reason is maybe we want so like a freelance so maybe we should just put pay amount something like that or gross pay amount something like that that might be a little bit more accurate because I'm thinking of having a lot of different pay types could be freelance it could be you know part-time or something like that so okay so that's kind of basically it I'm gonna have a picture here so inside here we'll have a picture and we can also add in our background so let's go into the page layout I've got a background set up for that and I created this in mid journey and so we'll just grab that background and then it's right here and I'm going to insert that in there okay so that's nice we can remove the grid lines here so I'm going to go into the view and just remove the grid lines okay so now for our headers I want to give them a distinct color so I'm going to hold down the control here for all these headers here I want to give them one unique color here and so I'll just continue to hold down this this and this Okay so we've got all these headers and then what we'll do is we can use Ctrl 1 here and to format those cells so we're going to use a fill and I'm going to use a fill effects and then we're going to go with a medium to dark light so we'll use this darker color here and then this medium color here clicking OK and then okay now we have the sub headlights some of these have Subs so we're going to use that I'm going to hold down the control for these subheaders and I want to give them a little bit lighter of a color so those that have subheaders we're going to add it in okay so that looks good I think those are the only ones with the subheaders and so what I'm going to do again Ctrl 1 here and I'm going to use the fill effects here and then what we're going to do is we're going to go into this color here and then we'll just use a lighter color here this one here so that's kind of a letter color okay so that looks good now I do want to do format so I'm going to use the borders here so I'm going to hold down the control we have a dark blue theme color that that's going to help us with that and what we're going to do is I'm going to use this and I'm just going to hold down the control and I want to put a thick border around all of these fields and we're going to use even these here and the pay details here so that there's some consistency around that and let's see this one too right here okay great so now what I'm going to do again Ctrl 1 again using the Border here I'm going to use I'm going to be using this theme color this darker green and we'll use a little bit of solid thick border all the way around okay that's looking good so now what we're going to do is we're going to add in some Fields so we need the payroll date we need the status the pay from date and the pay to date I want those in white because those are going to be user entered so we'll set those in fact I'm going to drop this down we'll pin it down here and we'll set those to White a few other fields that we're going to focus on the white we also want to know the PTO taken so this is going to be white and the overtime rate and the pay amount so those are so much flexible the user can put in there so we'll give those a white color too all right I think that's looking good we're going to save our work so far and then we'll continue on with the formats now I've already added some conditional formats in here I also need the base field color so what I'm going to do is I'm going to color for these basic fields and then we're just going to go all the way down here and give it give them a base field I don't necessarily want to put that background image on that so what we will do is we'll have just a basic color on that and so for everything that's not user and editable whether it's a formula or if it's a label or something like that we'll give it that base color here okay so that's good for now we're good to go on that and then what we'll do is we'll set that base color and I'll set it to this color right about here so that looks good right just like that okay now we're ready to add some borders and I'll go ahead and add them real quick just to speed things up make things a little bit quicker on this training okay there we go we got some borders added now that saves a little bit of a time now what I'm going to do is I'm going to add a shape for our employee picture and I want it somewhere about right there so now as opposed to just putting in an employee picture what we can do is add a page sure to this background so I'm going to use a shape outline which is going to be our consistent color here we can use a shape fill of let's just say our main background but that's important now the idea is what I want to do is add a picture for you now I've also got a challenge for you something I haven't been able to figure out so maybe you can help me with that it's kind of a little bit of code I'll show you what I mean by that I'd like to put a default picture in here now I can fill that now I can use a file path and I can use that default picture and I'll show you what I mean by that pictures we're going to use this device and then I'll just grab the employee picture now now it's going to grab all the pictures from this folder which is fine it's where I want my pictures located but I'm going to insert this picture here this is the one I want to use now in Excel we have a cool ability which is what I want to do I can copy this and then I can right click here and I can use Ctrl F1 and it's a really cool feature but I don't know how to do it in VBA I looked it up I honestly I want to be able to do this in VBA so we can use a picture or text fill and we can use the clipboard look at that how do you do that in VBA I do not know now of course I can fill this with VBA by browsing for the picture just like we're going to do with the pictures I can browse in the folder and I can fill it with this exact picture but I wanted in other words the reason I want to do this I want to put this off to the side somewhere I want to copy it and I want to fill it when there's no user picture I want to fill it with that without browsing without browsing for it I want to be able to copy it just like I did here and paste it in here but all I want to use VBA so I'm not sure how to do that but keep that in mind keep that in mind see if we can get that done if you guys know how or I did research it could not find it but if it's I don't I'm not sure if it's available or doable but if it is I'd sure like to be able to do it okay so let's continue on now what we want to do is I want to I got to use a picture let's give this is very specific name we're going to call this EMP and then lpic now we're going to fill this with the user picture now that user picture is going to come from this folder here and what we're going to do is we're going to then take this picture combine them and we're going to fill it with that now certainly we could have a default picture name and then fill it using the file path but my point is when you get this file you're not going to have the same file path as me and so it'd be nice to fill it with that automatically on no picture just an idea not a necessity okay so here's what we are going to do we want to be able to have a list of payrolls here I want to have some more titles here I've got some information here that I want to have I want to I'm going to copy this here and I'm going to paste it down right in here and that's going to make it a lot quicker and then what I'm going to do is I'm going to put in timesheet entry so time sheet entries I want our time sheet entries to show up here now what do we want I'm also going to copy this item here I'm just going to paste paste the formats here right here and then we're going to add in some information here so I want the entry date I'm going to need capitals on that that means which date the time she was entered so that's important actually we're going to put the entry date here I want to know that and then what I want to do is I want to have the ability to have the time in and the time out so put now the time in and the timeout are going to be date and times together time in and then time out okay so once we have that I also want to have the ability to add in the regular hours and I also want the overtime hours if any so we'll call this OT hours automatically calculated through formulas then I want to know the total hours work and we'll be able to come up with that formula very very soon okay so these are all going to be formulas these are going to be available so we're going to have that so we'll keep this static here so next up what I want to do is I also want to have our selected parallel so I'm going to copy this header here I'm going to paste it down here then I want to have the selected pay period employees those in which we're going to be paying for the paper I want to show the employee type so show which employee types in case we want to filter just as I showed in the show employee type so we're going to have a type this should be a drop down list in that cell already that's exactly what I want I'm going to call it that white and we're going to call it this white because those are going to be user entered users are going to be able to filter for new payrolls the list of employees and I want to put the pay type in here okay and I could just copy this and then I'm going to paste those formats here a little bit quicker and then paste the formats actually we can do this here actually we'll just do this and then what we'll do is we'll paste the formats here okay very good so next up what I'd like to have is here some headers so here we're going to put some headers so I'm going to copy this here and I'm just going to paste in the formats here right here and paste those formats here it'll make it a little bit quicker this one's going to be merged and centered I want to put the employee name so I'm going to merge instead of this and then this one is where we'll put employee name so employee names for our list of employees next up I'd like to know that what is the pay type of that employer salary how are you perhaps something else I want to do the gross pay once they are paid once they're paid the gross pay will show up here if they're not paid nothing will show up and of course the net pay what what amount are they going to get I want that displayed right here okay very good the pay type should be here and this one should be should not be Merchant centered here we need a data validation here so we need to keep that pay type here this one's going to be colored white okay good so that's exactly what we want let's reset the border on this one and we'll do the inside borders so you can use it control one I'm going to search all the way around the border and then we'll use the inside borders here that'll be sufficient okay I like the way that's looking so our employees will show up down here our timesheets will show up here we've got enough for everything and our drop down lists are going to be up here now I do have some drop down lists but I don't have any data for that we're going to be adding that in of course so not to worry about that okay so let's go ahead and start with our admin section this is a very important section as we're going to keep our hidden information here we're going to keep that available because we need to know what's going on with our workbook so we're going to start out right here and we'll add that information in okay each payroll will have its unique ID so we're going to call this selected pay ID want that one there and next up I also want to know what did the row that's associated with that the database are selected pay database row if we look in here in the payroll each one has a row they have an ID so we want to know which one we set those formats all the same I want to make sure that we know the row that's associated so we're going to put that here I also want to know the next available payroll ID so next payroll ID all right now I want to know which we're going to be selecting on specific payroll rows here so I want to know the selected payroll row selected payroll row and then also I want to know the next pay from date next pay from date so this is going to kind of help us out if I know the pay from date so that we know all the payrolls we can automate that next pay from date so I'll show you that's going to happen with the formula okay next up I also want to know the selected employee ID each employee has its own ID I want to know which the ID and I also want to know the employee database relatively employee database row what is the row of that database and I also want to know the next employee ID so when we create a new employee I want to know the next ID that's available for that employee after that I want to know this selected pay employee row so in other words when we pay an employee we select an employee from this area here on another row that's associated with that next up I want to have the frequency divisors frequency and I'll go over that with you frequency divisor and that's going to help us with the frequency and basically the frequency divisor is what whatever's located here so if it's weekly there's 52 in a year it's based on a year there's 26 bi-weekly month by monthly every other month 24 and monthly 12. so this is that device that's going to be here so that's going to help us determine the next available date so we're going to have that frequency advisor I'll skip a row and then inside here on the 13th I want to focus on some the selected employees so we're going to call this the selected paid employee database row so that's going to be the select so we have our selected employee here and I want to know the select employee database route here so I want that here I also want to know the next paid employee so next paid employee ID and I also want to know to a paid employee if we look at here we have a list of paid employee lists so we have paid employee ID every time we create a brand we pay an employee it gets a unique ID along with the payroll ID so I want to keep track of these IDs as well so I want to know the row that's associated with that okay so that's the next paid employee ID and I also want to know the pay this is going to be a formula pay and I'll go over this one in a bit PTO item row if I've created a PTO for a specific pay period I want to know what row it is associated what I mean by that if you see if you take a look inside our pay items here individual pay items you see this is a PTO paid item I'm going to want to know this was created on payroll id5 the pay date it's got employer ID one the employee information the fact that it was a personal time off the date that it was in the number of days so we're going to keep track of that as well okay next up I also want to know some information on the time into right if this time intercepts this we can I'm going to add some more features I think on patreon where we can actually edit these timesheet entries but that would be kind of cool too so I'm going to put the selected and then this will be the time entry ID this one is not in the selected time entry database row and I also want to know the next entry so that means when we were able to add new entries I don't I didn't really work too much on that as far as adding new time entries but it's certainly a correct way you want to add additional features to this another so we don't have so much ID time on that next entry ID and the selected entry row so what we want to do is we've got a lot of things here so let's color code them a little bit so that we can separate them so as far as our payment information that's going to be up to row six I want to color them in fact let's go do the borders on all of them here let's just color all these borders and we'll do all the borders around here so we can uh distinct now as far as the pain runs these I want to give a color of let's say light green drop this down and once again showing the tabs and commands I'm going to give this a light green color next up I'd like for the employee information I'm going to call it that a yellow color this helps us differentiate between different admin information so it's kind of nice this one this is our PTO and our paid employee information we'll be going over that and seeing how that applies would give this a green color here and then lastly for our timesheet information we're going to be giving out a pink color here or whatever color suits our needs here okay that's good that's different as long as it's different the colors don't necessarily matter we can drop this not this one here uh put this up here for now although we're not really using that next up I want to have some employee types here so here in B25 I'm going to put we'll call this employee types so what I want to do I want to have a unique list of employee types and I also want a unique list of pay types now what is that list going to be well it's going to include I wanted to I have a list here if we look in here I have a list of employee types here and I also have a list of paid types here but what I want to do is I want to take this list but I also want to add all types or all pay types or all employee types so that's exactly what I want to do here so the first item here is going to be called all within brackets all types I'm gonna do exactly the same thing here all types so that's sufficient all types but then after that what I want to do is I want to add the remaining types but I want it linked I want the remaining ones linked to whatever's here so as far as employee text what I'm doing is I'm going to copy this and then what we're going to do is I'm going to paste in the links so I'm going to paste special and then paste those links here now likewise inside the admin I'm going to do the same thing here pay types I'm going to copy that and then what we're going to do is we're going to paste and again paste those links in here we'll give those a unique color as well so I'll color the headers a little bit differently and so that we can see that we can differentiate them and then here down here we'll just give these colors so these are unique lists and that's very important because I want these unique lists to be used for our filters so what I want is a unique list here so because these filters here I want them to show up look at that it showed up why did it show up now it wouldn't show up before because I've already created a named range for this area let's take a look at those named ranges what would they be we're going to look in the data validation we're going to look in the data validation right here we're going to see that it's employee types with all okay if we create that we see we have a list of employee types but I want to take a look inside that employee types with all so inside the formulas the name manager we're going to go employee types with all we're going to Tab out and we see the dancing ants around that and basically what we're going to use you can use an offset formula now we're starting inside the payroll a26 that's our first row but I want to count but I don't want to count everything inside here because they're linked so I need to count what's in the admin and we're going to add one the reason we're adding one is because it includes all type now I did exactly the same thing for our pay type so employee pay types we have with all as well so if we take a look down here pay types with all is right here so we've done exactly the same thing here so we created a named range so basically all we're doing is I'm going to start here with that offset I'm going to count how many are in here and then what we're going to do is we're going to create that name range so that way then I put both of those named ranges directly in here and I put it in here so this way we can filter down by employee types so when we create a brand new payroll we only want to show maybe we only want to pay the HR staff maybe we only want to pay a certain type of employee we can do that or maybe we only want to pay our hourly or maybe only our salary we can do that if we have a large company we may not want to pay everybody at the same time we can do just that okay very good so now what we do is we've got that let's add some formulas in here let's go over some of the name ranges that I created those name ranges you can help us with a few of the formulas that we're going to be putting in right here okay back into the name manager we go and we're going to look inside our first thing what I want to do is that pay ID so we're going to look at the payroll ID if we go down here we see payroll ID and basically we don't need to go into every single one because we understand that we're going to use offset formula and we're going to use that so we have one for payroll ID here and it's the name range for that so what I want to do is I want to create a formula that's going to determine when the next one and the row Associated so if I've got pay ID 1 and I want to look in the row here I know it's on row 4. so we needed a formula to help us with that we're going to use equals if Arrow can wrap it we're going to use the match formula and we're going to look up this PID we're going to use that same payroll payroll ID named range that I just showed you which is right here I want an exact match so I'm going to put 0. I want to add 3 because our first one starts on row four if there's an error just show empty okay the next pay ID again equals in case if air I can wrap it around I'm using the max formula and then this is going to be the payroll ID so payroll I've got a lot of named ranges here plus one I want the next one if there's no data it could create an error so we're just going to default that to one okay so what that's going to do so we know the next payroll ID is seven if we look payroll uh payroll ID is on row four and the next one's seven so if we look in our payroll list we see the next one will be seven and we see that number one is on row four so we're good to go on that okay selected payroll now I've got some conditional formatting I want to know the selected row I've added some conditional formatting it's already here because it's creating it takes a little bit of time and we already have a lot to cover in this training so I've created a conditional formatting it's going to be almost the same throughout alternate rows even rows gets this color if there's data in column D odd rows get this color the selected row based on B5 get this gets this color so when we simply add any type of data here we see that it's automatically colored and when I added put in that select payroll is automatically going to be colored that's exactly what I want okay very good so we see that that conditional formatting and we want to know the next pay from date and so the idea behind this is that when I create a brand new payroll we need to add those buttons we'll be doing that next when I create a brand new payroll I want that from date to be the next available date well how do I know what that is if we take a look at our payroll list we see that it's not really in the best of order but we can look at the maximum from date right and what we want to do is we want to create the next available one so what I want to do if I know the last payroll that I created let's say it was August 15th so from August 1st to August 15th when is the next from date the next from date is probably going to be August 16th August 16th right so I want to automate that so how do I do that what I'm going to do is I'm going to take the maximum of the two dates and I'm going to add one to that so if we take a look inside the name manager once again we go into the payroll here and we see that we have a payroll to date right here this is a named range on all the two dates so what I'm gonna do is I'm going to get the maximum of that and I'm going to add one to that so let's take a look at that and see how that works so we're going to go back into the payroll I want the next pay from date it's going to equal and we can use if air on that just in case maximum payroll two days so they all have to be um dates or or numbers plus one okay otherwise if there's an error we'll just put the current date that should be sufficient okay there might be an error no data okay so let's take a look at that age 16 that's exactly what I want that's the next available date so that way when I create a brand new payroll I know exactly what date to put in the from date okay very good now I want us to want to know the employee ID so if I select an employee ID I want another row that's associated I've got a named range for that equals I'm going to use if air as always I'm going to match it I want to know what row it is based on that so we're looking up here we're looking up all the employee IDs so we have that I want an exact match we're going to add three just because our first one always starts in row four if there's an error we're just going to put empty or nothing okay perfect next employee ID same thing equals maximum if error of course maximum of our employee IDs because when we add new employees we need to to figure out the next employee ID number so we're going to have plus one on that if there's an error we're going to default to one that's going to get us to 34. Let's double check that if we take a look and we see our next employee would be 31 and we see that employee number one is on row four so we know that is correct okay I want to know the selected employee row if we take a look in here if I put in 10 in here or 15 I want to know what let's put in 15 here I want to know what row it's been so that's already conditional formatting I already have conditional formatting the same as what we've done before exactly the same those odd and even rows it's already here along with the selected row based on that I also want to know the frequency divisor now this is going to be important because I want to understand exactly what that is so basically what I want to do is I want to know this number right here based on whatever payroll frequency has been selected here we have something called a pay frequency this is how often we're going to be paying so I want to know this divisor here so that's going to help us determine how many payrolls are in a given year so it's going to be important moving forward I want to know if it's bi-weekly 26 so I want to Index this I want to match this and I want to return this so actually we're going to Index this one right here so how do we do that so I'm going to show you right now so we're going to use equals index and what am I indexing I want to return these divisors right here or these number of payrolls within a year so that's what we're going to be indexing and what row again we need to determine the row based on a match what are we looking up we're looking up this one we're looking at pay frequency and we're looking it up in this array right here which is also called the frequencies I want an exact match so we're going to put zero then we want columns one okay so and then we probably could wrap that around if error so our frequency divisor is 12. perfect that's exactly what I want because if we're on monthly we know it's 12. continuing on so we've got that set up we're going to save our work moving on I want to know the selected paid employee database row that will go go into a little bit detail but that's not a formula we're just going to put when we pay an employee there's a database row that's associated this is a row row 4 payroll ID right this employee was paid here's the database roll for this is the row that I want but only once they're paid if they're not paid yet there's no Road that's associated when they get paid all the information about that payroll is going to be stored right here so this is our paid so we're going to have that that'll be VBA that places that road directly in here so I want to know the next paid employee this as we see here inside our paid we also have some unique IDs the next one would be 158 so I want to determine what that is and I want to use a Max formula to do that equals if air again wrapping around if air we're going to use the max formula this one is going to be called the paid employee ID I want the next one so we're gonna use plus one and if it's narrow just defaulted to one so we see the next one's 158. okay PTO we're going to leave this one empty right now I'll be covering that one in just a bit select like the time entry ID we can probably leave this blank and then the next entry database row these we probably won't be using just yet so we'll keep those that's kind of for the future when we add additional time add or edit time entry details that one I'd like to do but not quite yet okay so we're pretty much good to go for a while we'll come back to this as we need it now what we want to do is we want to be able to add these button sets we've got to add some buttons so that we can get those features going on so we're going to insert here a shape and we're going to be insert we'll start with a basic shape and then we'll add it we also need a logo here but we'll add that in a moment okay so our shape fill is going to be our color here our font color our text fill here is going to be this white and we'll have no text outline on here so no outline perfect and we also want no outline on the shape too okay so we'll put no outline now what I want to do is I want to add this but add new payroll this one will be done this will be used to add new obviously and then what I'm going to do is I'm going to write put that in the middle and make it a little bit bigger so I have room for the icon we'll be adding an icon here so I'm gonna place that right about here I'm going to now that I have everything just the way I want I'm going to use Ctrl D I'm going to duplicate that I'm going to place the one right here to save the payroll delete payroll certainly an option but I didn't put it in this training so if that's something you want to see that'll be on patreon okay so very good so we got save payroll and I'm going to make these a little bit uh we don't have too much height so I'm going to put set the height at 0.26 on that should be sufficient for our purposes here because we don't have a lot of room okay so we have save payroll next up I want to add another button here this one's going to be for ad employee so I'm going to use Ctrl D and I'll place that right here and that's going to be add employee so actually let's put these in capital letters I just like them a little bit better a little bit easier to read so I'm going to make it my sample wasn't consistent but I want to be consistent here add new payroll it's a little bit easier to read in capitals it stands out save payroll okay and then this one's going to be here add employee make that a little bit bigger we need room if you don't see that third you just need to zoom in a little bit there's that middle one that we see now so we can make that and next up I want to put print paste up again we'll be probably adding this feature in our patreon because I can only squeeze so many features into these strings but I want to make sure you get a good start paid stuff okay so that looks pretty good I like that let's uh take a look so that there's not too many buttons on there but I do want to add the ability to go previous next previous next employee so to do that we need some a little bit different shape on here but I certainly want them to make sure that they're the same size so how we going to do that okay well we'll just go ahead and insert here and some shapes here and we'll use this here we're going to use the arrow right here I like this one right here so I'm going to add that in here I'm going to give it the same height same color everything else and I want to make sure that it's consistent with our current theme so we also don't need a border so the height on this one is 0.26 I'm going to make sure this height is the same also 0.26 we are going to do the same fill same outline the same text here and then I'm going to bring that right over about here we'll move the employee we need to add that information this one's going to be called say save and next so we're going to put save and I'll use abbreviation here next Ampersand okay now I also want to make sure that there's enough room so in fact let's go ahead on this one we want to make sure that we use Ctrl 1 that the text we need to make sure here that our text inside that that margins we don't need the margins on that so I'm going to zero those out that gives us a little more room I want to make also make sure that we're centered on these okay so that way now we'll just make it that shape a little bit bigger so that it fits it all in so 1.2 okay so saving next we've got that I'm going to place that right here I'm going to duplicate that using Ctrl D and then I'm simply going to reverse it so we're going to align this one actually we're going to rotate it and then we're going to flip it horizontal and then I'm going to place this one right here and this one we could just call it previous so okay very good I like that I like to make these the same size but I want to shrink them up just a little bit if I can let's see if I can squeeze that in here okay that looks good now we're ready to line up we've got our button set so we're ready to add in our icons and our logo here so I'm going going to insert some pictures here and I've got some saved up if you want these pictures they'll be available either on YouTube Community or in our patreon so we'll have both of those for you and they're going to be right here inside my icon so icons in place so I'm just going to add all of these and I also need to insert that logo too so I'm going to make these uh 0.2 in height so that they're all consistent again I'm going to insert the picture and this time it's going to go to the logo now I like to create my buttons and then I like to add in my icons because this way that the icons are always on top here's my logo so I created this with mid Journey so put that down here created the background with mid Journey as well okay so we're going to click add new so this is going to use we're going to do this here in fact I'm going to use this this one duplicate that I'm going to use it also for ad employee and then I need to edit the employee I forgot one button here so I'm going to duplicate that here and we're gonna I'm not going to put in anything just an icon here because we don't have a lot of space I'm also going to make this a little bit smaller so this one's going to be 0.25 and also the same height 0.25 and so you see when I add them watch this when I try to put this icon the icons underneath so I'm just going to move it to the top that's why I generally do that so this is going to be used for our edit employee I want to select both of those so I'm using my selection I'm going to Center those and group them this one is going to be used for headed employee and I'm going to put that right about here add employee add add new payroll so zoom in a little bit we need a little bit uh bigger on that one and we can then just line that up okay so I like that I'll make it a little bit bigger we have some extra space here so it's no problem now what we want to do is we also want to make sure that we're centering those so I'm going to hold the control and put them in the middle group them together saving the payroll we're going to use our check box right here and we it's always good to zoom in so you have all the controls of each individual shape then we're going to then group so each one you want individually the previous and next will not get icons so we don't need that and then the add employee again we're going to put those in the middle and also group those together as well save it next and then print I think we add the printers right here so we're going to print that paste up and then we don't need the delete icon there's nothing we're going to be deleting in this training however in your upcoming trainings like I said did the payroll deleting payroll which is kind of I don't know if it's kind of scary can you imagine someone deleting all the payroll so maybe something like that should be only with an admin password so it's kind of dangerous okay so next up what I'm going to do is I'm going to select all these and I'm going to make sure that they're all centered here ingly and then I want to make sure that they're in the middle like that okay that's what I like now if you're good to go with that we like that what we'll do is we're going to group them all together and most importantly we want to make sure that when we go into the properties here we move but don't slide so we're going to do it with this one as well although it's small here but we're going to move it on size okay very very good so I like that that's looking good and now what we can do is we can zoom out and take a look and see where we are okay very nice I like that let's go ahead and add some interior your borders on these because I would like to do that so we're going to add hold down the control and then we'll just add some interior borders we'll use a dotted line which is going to be consistent with our theme and of course the same color and that'll give us a little bit Separation on that so we're going to just right click here or we can use con Ctrl 1 which would work just fine as well I'm going to use this dotted line and not on the right and then all we need to do is just update these Interiors here any other anything else left here which is these three here four here and we'll add that same one on the right side on both sides the left and the right in case it hasn't been added in okay so that's all we need to do let's go ahead and put that dotted line in the middles now that's going to give us a separation so we'll put our hourly summary what am I going to put in here I want the total regular relative regular we got enough space regular hours next up I want to know the total overtime hours total overtime hours and I want to know the total regular pay total regular okay regular pay based on the regular hours and of course I want next up to total overtime pay total overtime pay and then total gross pay perfect now next up I want to continue a little bit of a continuation of that so this is going to be again the total same thing total gross hey we'll put in the summary and I want to know the total tax deductions what are the total total tax deductions after that I also want to know the total other we could do total other deductions we can put deductions here and then total other additions total other additions lastly I want to know the total net pay total net pay okay very good so what is it going to be so our total regular hours how many regular hours is going to be a summary of this one here so we need to know that so I'm going to put that maybe we'll wait till we get a little bit of data in here in order to add those formulas so we can see the result saving our work so let's focus on getting some of this data inside here now we can do that first thing what we want to do is I want to be able to select a payroll so if I select a year here change the year here I want that payroll to show up right here how are we going to do that we're going to do that with a macro of course on change event of E4 that macro is also going to run every time we add and save a new payroll it's also going to run so we can do that we can also assign some macros to these buttons so let's add the macrame but why don't we assign the macros here so we can get that done with so I'm just going to hold down the control and I'm going to right click and I'm going to use n or we're going to end up payroll we need to sign a macro add new so we're looking for something called payroll add new so we're going to go down here right here is what I'm looking for payroll add new we'll be going over these macros this one's called save payroll so I'm going to right click I'm going to look for payroll saver update something like that go down here that I've already created payroll save or update right here next up I want previous this is going to have to do with the employees paid so again we're going to then assign that macro we're looking here for pay employee and then I'm looking for something called previous that's what I want adding employee I'm going to add a macro to that and then what the macro to that this is going to be employed at add new so here's the macro we're looking for next up I want to use Save and next so that's going to be for the payroll save and next that's going to be the macro name so if we look down here in the payroll and we see something called payroll pay employees saved next lastly we don't have macro on this this one we want to edit the employee so I'm going to sign a matter to that something like employee edit here that's the one I'm looking for okay very good so we've assigned our macros to our buttons of course those macros we're going to go over if I were to write these macros during the training this training would be seven hours so we can't have that so we've got to have some kind of limit on that although they've gone pretty long this one hopefully not too so what I want to do the first thing is I want to list those payrolls for the given year if we take a look inside the payroll list here we've got a payroll list now what I want to do is I want to know all the payrolls for a given year so how do we know that we have the pay date this is the date that we're going to be filtering I want to all the payrolls so what we need is we need a filter and we can use it right here if we take a look inside this payroll we look at a named range this is called payroll year so I've already created a named range for this so I want to know basically January 1st 2023 all the way to December 31st 2023. I want to know any payroll that landed those payrolls I want to First have them appear here and then second bring them whatever's in here bring them in if we look at here the short date it'll automatically update until we see all the payrolls so that's exactly what I want to do so that create I created some criteria here it's going to be greater than or equal the date we're using a date and what is the year it's going to be based on the payroll year January 1st that's going to be greater than or equal to I also need less than or equal the last day of the year less than or equal the date payroll year December 31st here's our criteria here's our results so we need a macro to do just that when do we want to run that macro I want to run the macro when we make a change to E4 so we're going to look into VBA and see just how we did that so we're going to go into the developers alt f11 is also the shortcut there we're going to go into the Visual Basic and we're going to focus now I've got two workbooks I got our sample which is over to this side so unless that gives us a problem we will keep that open but we're going to focus pretty much on the payroll so now I've got some macros that they have been commented out so that while we're designing we don't have to worry about them activating we want to focus on the worksheet change event that's the one we're going to be focused on so what I'm going to do is just going to take this we I've commented all that I'm going to uncomment those out and we're going to focus on this one E4 when we make a change to E4 and E4 is not blank we want to run a macro called payroll load list if I right click here and I click definition it's going to take us into the module called payroll macros payroll macros the first one we're going to focus on the payroll load list all we're going to be doing is just a few things the first thing what I want to do of course is clear out any data here that payroll ID I'm going to put that payroll ID right in here here in fact what I'm going to do is I'm just going to change this instead of accustomed to General that way before it's not before it was hidden now it's not going to be hidden I want the payroll ID here in column C the payroll name that payroll date and the status of that right here so how are we going to do that well the best way to do that is to be able to create that advanced filter I also want to notice the selected row here is nine we've got that selected row B5 I also want to clear that as soon as I clear that out it's going to automatically move then we will find it again and we will select it again during the macro so this macro is going to do just that notice how it opened the wrong workbook it's frustrating when I have multiple workbooks open it open the code for the wrong so but I kind of like to keep that workbook open to keep us as a guide okay so payroll load this we're clearing out that selected Row in B5 we're clearing out any of the data so it's going to clear all the existing payroll we're going to focus directly on that payroll list next that's the one we want to focus on to run our Advanced filter which is right here so that payroll list that advanced filter will determine in the last row I'll move I'll speed up a little bit but you can welcome to slow this down if it's less than four we're going to exit the sub we're running our Advanced filter all the way from a through F we don't need to we don't need this information I just needed to go through we could do G but we don't need we don't really need that information in a results but it's kind of good habit so the criteria is J2 through K3 you saw that here our payroll results coming from n through P2 we're going to determine the last results row I want to sort it I want the most recent on top but if there's only one row of value we can skip the sort so one row skip to start no data exit the sub we're going to sort it based on descending I want the highest down to the lowest based on our pay date which is located in A3 this is our range once I've sorted it based on that pay date then I want to bring it in to our payroll list then what I want to do is I want to select that row it's going to bring in that payroll ID so we can bring all these payroll IDs it's going to bring them directly into column C then what I'm going to do is I'm going to look in column C if I know that our selected payroll ID is number one I'm going to look in this list if it's found number one I want to take whatever row it's found and I'm going to place it directly in five and all that's going to happen when I make a change here so if I double click and tap out of it that's exactl