Have time clock settings here where we have custom time clock messaging for clock in and clock out along with break and a whole lot of time clock settings we have added and all of this is going to generate data into our time clock list automatically so it's going to be a great training let's get started already welcome I'm really excited to show you this week's training part 10 we're gonna be focusing on the time clock feature and this time clock feature is going to be used on a dedicated machine since.
This is going to be a shared workbook this particular time clock feature we'll be able to use on a dedicated or a specific machine whether it's a computer or a laptop and it's going to give the ability for employees to clock in and out using this very simply and it's gonna replace some expensive machines that are used in this manner but before we get started this amazing clock feature that I just love I did not do but I want to make sure to give credit to the person who did create this and it's a good friend of mine by the name of George lingu who does some amazing.
Stuff in his website called Excel unusual and this is way beyond my capabilities and he does some incredible things so I want to make sure to give a shout out to George he's done a fantastic job with this clock and he's got a ton of amazing Excel features all done in Excel all very unusual very amazing so make sure you go ahead and visit his website for anything he's a great guy and he's a part of our group so he's he's always three.
Some amazing incredible things to excel that that are way beyond my capabilities so he's just done some incredible work so he also did this time clock so I wanted to make sure that I gave him credit for that because he did a great job on this having this time clock in Excel so it makes you check out his website at Excel and usual dot-com and pay him a visit George has done a fantastic job so he did this clock and that's going to give us the ability to clock in and out and give us a visual representation of employees to clocking.
Out and this particular screen would stay up on a specific machine and all employees would have to do is just sign in or or put in the number or if they have a barcode reader on their ID cards that number will be translated and then all automatically or of course a thumbprint drive and we'll go over that in a little bit but basically all the number has to be done is entered into this for a really easy so if it's already been entered it'll tell you it's been entered if it has not been entered.
We've clocked them how one zero zero one it's gonna say okay you have been clocked out at one ten o'clock for your break great alright so we've got that now let's go ahead and make a quick change on that and here it is right here automatically let's just say that o'clock change that to 9:00 a.m. and of course only admins will have the ability to make these types of changes employees would not have it so let's say we go ahead and clock them out one more time automatically it's recorded now we have clocked in from your break at 10 o'clock.
For total break of 1 hour 1 hour so and the speed at which that pops up in hides I can you can control and I'm going to show you how to control that great so now they have if we look in the time clock now they have been clocked back in from the break at 10 o'clock now let's say we're ready to clock them out for the day let's say we'll change this to 9 o'clock well change this to 8 o'clock just to give it some additional time on the schedule so that we can see and let's go ahead and change this to like 4 a.m..
Just so that they have enough hours on their schedule now when we go back into the time clock we type in 1 0 0 1 and all of a sudden we see that good morning Asian you have 4 working hours of for 24 so thank you very much so it automatically updates and includes the total time and includes the time that they've clocked out and we can do all of that through some settings in our admin so let's go ahead and get to the admin first I'm going to stop this clock.
And that way you have the ability to start and stop it I'm going to show you that and let's go into the admin screen and show you what we have done here we've added a time clock settings here and we've added additional features what I want to happen I want to have some customized messages that give the employers a very personable feel when they come in in the morning or when they leave in them in the afternoon or evening and I want him to be customized not only based on the time of the day but based on whether it's been clocking in or clocking out or if it's for a.
Break so we've added multiple fields here so basically what I want is if the employee is clocking in before let's say 11:00 a.m. and of course this can be updated through our drop-down list here I want this message to come up and I'm going to say good morning and then instead of the employee name I want their actual employee name to be stated you have been clocked in at and whatever time the actual times you can place have a great workday and of course as we change this the message that will appear will also change and if they have.
Clocked in after specific let's say we change this to 6 p.m. then it's going to
Say good evening you have been clocked in at have a great workday or whatever and so anything in between which is automated this is a formula it's automated it's going to be a locked cell anything between this time and this time will automatically get this message so we have the ability to customize the announcement message when they clock in of course we can change it this kind of if you have a specific announcement you want to make to employees or something we can control.That so let's say you have a holiday coming up or something like that that way you give the ability to make changes to the messaging that they get and remember the idea for this time clock is to have it on an independent machine this will always be running you could use an old computer an old laptop or just a screen and it doesn't even need to have a keyboard it's fully automated so that this screen will stay up let's say on the entrance of your business or something so the screen and then you'll have maybe an ID card and my idea for.
This basically is to have some sort of an ID card like a credit card terminal here like a USB card here one of these types of readers will replace the keyboard so where as I'm typing it in if you have an ID card with their employee number in a barcode format something like this would work great all they would have to do is scan their card in and optionally you could use a fingerprint reader so that once we use a fingerprint reader that's also USB you.
Assimilate their fingerprint with their employee ID and so as soon as they put their fingerprint on the ID just goes right in to the keyboard or USB and it automatically types it out so that's kind of my idea for this it basically creates our employment and adds on a fully automated time clock feature that gives us the accuracy and the ability to employees to clock in clock out for the day or clock in or out for breaks on a.
Fully automated system and that is the power of Excel when we think outside the box so I really wanted to bring that to you today so I wanted to show you what is possible with this in Excel and so when they clock in or out it gives them that a bill and it's it's simple but it can be a little complicated because we have to add some logic into that we have to know are they clocking in are they clocking out for a break are they clock out so we need some rules in order to do that so.
I'm gonna show you how we apply those rules and I'm gonna show you how we went about and pulling that out so let's go back into the time clock setting so we have clock in customize clock in messengers we have customized clock out messages as well for specific times and the clock out message also includes eight total hours let's go ahead and pull that up includes a total hours so this way when we have total hours the actual hours that they've worked - any breaks are automatically added and and I.
Believe I showed that to you here let's just take a look at what that might look like let's go into the time clock list and okay we'll use this one zero zero seven this employee has clocked in he's clocked out for both of his breaks so he's only missing a clock out so we know if we type in one zero zero seven waits gonna we can start the clock and so all we need to do is one zero zero seven enter and it says good morning you have been clocked out at 10:40 for a total working hours of ten seven thanks so much and the rest it's a little bit fast.
Because you don't you want employees to be able to but we can control that time how long it appears very easily so that's no problem so it automatically added for this because it's before 11:00 a.m. right now it's 1014 a.m. here so at the time of this recording so that's why this message that's why it said good morning you have been clocked out at the time for total working hours not listed the total working hours thanks so much for your effort if it were between 11:00 a.m. and 5:00 p.m. it would have this.
Particular message or in the evening this and of course if they're clocking out for a break they would get this message you have been clocked out at for your break or it says you have been clocked in from your break at this time for a total break time of so many hours let's take a look at that let's see someone who is okay here we go here's a one zero zero nine he's been clocked out at 10 a.m. it's now 10:15 so if we clock me now they're gonna get a.
15-minute break so let's go ahead and take a look at what that when we enter one zero zero nine in the time clock so
One zero zero nine and it's gonna say you have been clocked back in from your break at 10:15 for a total break hours of 0.25 hours great so that works 0.25 a quarter of an hour so alright great so you see how that works and when we go back into the time clock list we now see that here at 10:15 they've been clocked in from the break so basically we can.Teach Excel to make the appropriate entry whether it's clock in clock out clock back in from the break or clock out based on a set of rules based on a set of principles and formulas now I'm gonna show you in just a bit so I'm really excited to show you that to you how we can how Excel with your program can be really really smart based on some rules that predefined rules that we've set and some of those rules we've added in the admin screen because we have to know for example back into the time clock settings we have some time clock.
Settings first of all what format do we want this in 12 hour format right now we have it in a 12 hour format so we look here it says 1016 a.m. but what if we wanted it in a 24-hour format well we would just type in back in here we just change this to 24 hour format and the in this case it's morning so the 8 just the a.m. is going to disappear so when we go back into the time clock will see that the a.m. is gone here and of course if it's after 12:00 it would show 13 you know or 14 or 15 right on this format.
But basically just the a.m. is disappeared from that or p.m. with in some cases so we have that so we'll keep it in a 12-hour format for now but you have the ability to customize that based on this so it'll just say here's some details 12 hour format 12 hour format would be 1:30 p.m. while a 24 hour format would be 1330 all right we have the ability if we don't want employees to clock in or out for breaks we could put no on this and if this is no for.
Example let's go ahead and take a look at the time clock Lister this is no let's say employee one zero zero nine has no brakes they've clocked in at nine twenty three and we have chosen not to add brakes and now what if when we enter this number into the time clock it's gonna automatically add a clock out time and it's gonna ignore any brake clock in so when we go back into the time clock and put in one zero zero nine it's gonna say good morning you have been clocked out at 10:17 for total working hours of 0.9 thanks so much so again it ignored.
Those brake times and if we look back in the time clock is ignoring and it added a clock out of 10 17 so it's very very smart based on some rules that we set and so we have that ability here to use them and also do we want to show the employees we may not use if you don't use employee pictures we can set this to know so that employee pictures don't appear on that if you're never gonna add an employee pictures that we can have the ability you put in no in here we also have to get very smart because we.
Always have to ask what if what if an employee forgets to clock out but the previous day how many hours should we wait before we say ok they forgot to clock out but it's morning time or whatever and we need to clock we need to leave this employee blank for example if if this guy forgot to clock out here at some point we need to say ok after 12 hours if they put their idea it's gonna be clocking in and we'll just have to show I'm missing you know we'll put up.
Mess saying you missed your last clock out or something like that so the admin would have to clock them out but but deem the application has to be smart enough to know that they have missed a specific clock in or out so you can set the rules here is basically in this particular rule what we've done is we've said okay if automatically clock in for the next shift when 12 hours have passed since the last clock an employee missed the clock out so we can set this hours whether it's 9 hours 10 hours 12 hours or 20 hours or.
Whatever so that means that gives us the ability to not mess things up so it says okay after 12 hours they obviously missed the clock out so use this to clock in for the next day so we use that type logic and then the same thing for breaks if they have clocked out for a break but let's say 2 or 3 hours or however many hours you set here they have not clocked back Infant break then ignored that particular scenario and clock them out for the day so for.
Example let's say let's say we're here let's say we're here and they've clocked out for the break at 8:27 right but it's now 10:20 you know almost two hours have passed and that means they've probably forgotten to clock in for the break so in that case let's just clock them out for example let's take a look at this and then admin would have to add in those hours so we'll put some kind of a notification for admin to say hey there's a missing entry here please make.
That correction so we're gonna do that so for example let's say we go back into admin let's raise this back up and go into the time clock settings and say let's say this is 1.5 and that means 1.5 has elapsed since the break clock out so they've missed clocking back in so that when we go back into the so for example already it's currently nearly two hours since they've clocked out so if we go ahead and add in one zero zero two into.
The place that should be a clock out based on our rule so when we click one zero zero two and it will automatically you have been clocked out at 1024 total working hours of and I got a format those total working hours will fix that and that's no problem so now you see it's been skipped you see clock and brake has been skipped and they've automatically been clocked out based on the rule that we set here in the time clock based on this 1.5 because it's.
Currently greater and we're going to go over those rules and so you can see there's a lot of what if what if they don't clock out what if they've missed so when all when you have all those types of scenarios it gets a little confusing in the code so what I want to do is show you a way that you can easily visualize those rules and map them out sometimes before you even write one line of code it's helpful to map the things out that you want the process to flow and basically it's a clock in clock out we're making this very easy for the.
User but that creates some possible difficulties within the coding of the application so in that case maybe we want to map that out and I'll show you what you could do to map that out and how that might work for you and so I've created a process flow just to show you how that can be made simpler so let's take a look at that this is the time clock process flow that I've mapped out the steps and basically I want to take you through the steps and when you're dealing with something that could be a little bit complicated you may want.
To create a map like this yourself so that you can understand what is going on and what needs to happen and this is really really helpful so so I've done this in Excel and you could do this with really any type of application Word Excel PowerPoint almost any type of application and so basically what the first step is the employee ID is going to be entered and I want to know the first thing I want to know is is this a valid ID is this a correct employee ID because if it's not I want something to.
Happen for example when we go back into the time clock if they enter something that is not correct I want something to happen employee ID not found please reenter ID I want a message to appear if they haven't entered something and I want it to go right back into that same cell so they can enter a proper ID or if it's already been completed I need to know that as well so that they can enter it something if it's entered like this we need to know but if they enter something that is correct we should have the ability to have that pop up message.
Correct so there's different scenarios based on whether it's been entered already into the day or whether it's incorrect so we have to make a lot of decisions based on that and this type of decision or a process flow tree really really helps us so the first thing we want to do is let's go ahead and stop this clock and take a look at that the first thing is it a valid ID if no then display the invalid message ID if yes then continue on first of all what I want to do then next is I.
Want to determine has this person clocked in or out on the current day I need to know have they already clocked in or not and so we can find that for example here in the time clock list what I've done as I created a time clock list ID and this is based on the date the year the month the day plus their employee numbers this is the unique time clock ID and this is gonna help determine whether they've clocking around so I need to look through this.
List and say have they clocked in or out on a current date is there an ID right if not then we should create one but there's one little trick what if you're working in a hospital right and you clock in at 11 o'clock p.m. the previous night and your shift is over night so there wouldn't be an ID for example let's say you've clocked in at 11 o'clock the previous day and all of a sudden now it's the next day so we should look also to the previous day and see if there's an open if there's an.
Open time clock entry for the previous day cuz it's possible that they may clock in at 11:00 p.m. and on the next day they want to take the break at 2:00 a.m. and come back at 3:00 a.m. and then finish their work at 9:00 a.m. or 8:00 a.m. so it is possible that we also have to look to the previous state to see if there's an open-ended time clock injury just in case this person is working in nature so that's also something that we have to consider and we can map that out too so let's take a look at what that.
Would look so is their time clock ID found here time clock ID found if yes then are we them if yes then are we using break clock out one that we have to set up some rules one how's the user enabled the breaks right are they using the breaks or not - have they clocked out for a break in so the if they've clocked in but not clocked out for a break let's go over what that might look like into the specific list.
Here for example in this list this employee has clocked in but they have not clocked out for breaks so we have to ask two questions one is this new is this here and this missing and the second question is here in the admin is are we using it for breaks yes or no so if no right if we're not using breaks then it's not so important right then we don't need to know here about this right.
So because we're not using breaks but if this is yes if the employees have yes we are using breaks and this is missing then we need to clock out and there's one more specific function so there's three different specifications let's go over this in fact let me go back into the admin and you see how this always goes back to the company information I'm gonna stop that because it's really annoying but it's important for for the future but let's take let's pull that out so go into the admin and on exit screen where she deactivate we're gonna.
Switch this so I'm going to comment these two things out that way we can maintain our tab on to that specific time clock settings so that when we exit the screen and we go back it's gonna remain there cuz we're gonna be focused on this so I've commented those out and if you can you can too if you don't like that particular feature that it returns back to the company information you can comment that out however we do want to use the find with melissap and we'll go over that so my question ditions three.
Conditions one condition are they using breaks yes or no if it's no skip breaks if it's yes then we need to focus the second condition is the second condition is is this empty is it empty so that's two and that's there's one third condition one third condition is the break hours is it more than this in other words the time that they clocked in the time that they clocked in here 10:31 is the current.
Time greater so for example if it's 12 o'clock or if it's 1 p.m. right now that means they've missed their break clock out so we need to skip this and put it here you understand that so if this is missing if this is here then they've missed it so we have to make those three considerations before we clog in so back to the mapping so okay so if it's a break clock out if it's if there's one of those conditions passes we're gonna add.
The break clock out to the table but what if it's no but maybe it's a break clock in so for example back into this let's go ahead and go back into the employees and let's go ahead and clock this person in 1 0 0 7 so we can do that into the time clock make sure that we have enabled breaks okay breaks is enabled good back into the time clock 1 0 0 7 let's type that in in the proper cell 1:07 ok now it's been enabled and.
The time clock will automatically start okay so that's 1 now he's been clocked in so now we can see into the time clock list that he's been added here ok so now you see he's been clocked out cocked out for his break so let's go ahead and continue on let's stop the clock and go back into our time clock here so now we've now this says break clock out yes so now we have continuing on what if it's clock in what if they're not what.
If they've already been clocked out but maybe they want to be clocked in so if that is the case at the break clock into the table so that is the next step so in this particular employee 1 0 0 7 we're gonna clock him in under specific scenarios so back into that so now 1 0 7 we're ready to clock him in thank you you have been clocked back in from your break and cut a break time of point 0 2 obviously not that much so now we know in our time clock now he's been clocked back in so the.