Hi Mike , Hope you are doing great .. I listened to the video it was really excellent . Am from India working as Hr , going to attend an interview on payroll .. Thank you soo much . Best Manju
This video is amazing. I have got a request. If you can please make another tutorial for the Payroll on daily basis, i mean where the overtime is calculated on daily basis instead of Weekly basis for each employee, i would really appreciate that! Thanks in anticipation.
You would have to make an entirely different template to look at each daily total. I do not have a video that shows how to make a template like that. Sorry.
This video is incredibly helpful. However, I'd like to know if you could show me how to do all this but to also include time taken for lunch AND more importantly when you're dealing with AM and PM time entries. If this could be included within this video you'd get FIVE STARS!!! Thank you so much!
loving the video's man. nice work. i'm still stuck with something though. i have made my own spreadsheet for recording my own working hrs plus my wage. so what i'm stuck with is night rate, i need a formula that will kick in night rate at the night rate times and stop it when it's not night rate hrs.
Hi Mike, Great Video! I have a quick question:How would you adjust this model for daily overtime. In other words, a random employee, lets call him Sam, does not work a 40 hour week. Lets say he works 35 hours. However, during the first 2 days of the week, Sam worked 10 hours a day and the rest of the week he worked 5 hours a day. Using this model, it would show him to have no over time when in fact, he will be owed 4 hours overtime despite the 35 hour total. How would you adjust for this?
Great video!! Have a quick, somewhat unrelated question: Is there a way of creating a "countdown clock" in Excel by taking the difference between the variable "=NOW()" function and a fixed future date, and getting the following formatted result: "Y:M:D:H:M:S??" Thanks for your help!!!!
do you have a video showing how to format cells for multiple different job codes? for instance if there are different rates of pay depending on the job, not just overtime or day vs night shift; i need a formula that will automatically pull data from a "job code" table or something like?
Hey i like your videos and i have subscribed your channel too. i have a question. if a person is clock out at mid night for example a person clock in at 4pm and clock out at 8pm for a break and then clock in again at 8:30pm and clock out at 12:15am, then how can we enter a formula?? because when i tried like ur way it shows me negative value.can u help me with that???
I have a question: I want to calculate hours for different shifts, evening, night & weekend, is that possible?'' will send you a link with Excel sheet on Google drive thanks for your help
Mike, if am using an if statement to determine an override pay rate, can I use the time function to compare the time stamp to lets say 9:00:00? =if(start.time
hello really awesome video .. i was trying to create my employee payroll sheet but i don't know when i calculating total hours value showing error please advise me to in format tab which i can select and send me link for download above sheet much better for me .....Thanks Wait for your kind replay
Hello! I came across your videos, and they are very well done! I have a dilemma for my calculations, though: I have a template timesheet that I downloaded from the Internet. It is set up to take my total hours from the 1st week in the pay period and add them with the total hours from the 2nd week (I'm paid biweekly.) What I noticed is that it is doing a function where it takes the total number of hours from both weeks and multiplying it by how much I make per hour, and the result is wrong. I use the calculator on my computer to do the calculation and it doesn't match. What's the problem?
hello good day can i ask how to calculate time sheets if the working hours is less than the designated time of work like supposedly need to work 8 hours a day but the person work only for 7 hours. How to reflect it to the time sheet if overtime or under time...Thanks
Hello there not sure if anyone checks this or can help me but how would I take out a half hour break without manually putting it in like you did in this video. I have schedule with multiple full time shifts that need a half hour taken out then part time shifts that don't. Let me know if you can help thank you.
I love your videos! Quick thing though, I see that you do overtime base on 40 hours. Anything after 40hrs it goes to overtime. I don't want to do it base on 40hrs. I will like to do it base on 8hrs. I tried to use the if() you have(=IF(B7>40,40,B7) but changing it to 8 instead of 40 but it doesn't work. I started 830 and finished @ 18:45. Total hours 9:45 with lunch taken away. I want the cell of total hours to give me 8hrs and the overtime to give an 1:45 hrs. What am I doing wrong?
now... what if your way of calculating wages is based on time and not amount of hours per week that you are over. say from 5am to 7am is one rate and then from 7am to 1pm is another rate? thanks
Hello, I first want to say thank you for the great informative excel videos. I am a employee that likes to keep track of my own hours and I have an spreed sheet to do so. I now would like to take it one step farther and would like to calculate my yearly hours. I get regular time over time and paid double time. How do I calculate all three? In this video that I'm commenting on, you show reg and over time. ( time and half) I hope you read this being that this video is from 2011. I am not sure how to contact you outside of this if there is such a way. Thanks again and I'll be looking for a comment back Alan Forbes.
I NEED HELP! PLZZ im trying to do something similar but more specific i guess. im a server and ive been working on an excel file to track my tips and sales percentages tip outs etc, it's very complex and im completely new to excel so it took me like two months. i'm trying to recreate an app that i was using but doesn't track enough details. in this app there are tabs on top for sorting the data yearly, monthly, weekly and biweekly and "all" and then at the bottom you can toggle between all of the data with arrows, between the 2 arrows there are 2 dates based off of the filter (the corresponding dates of the month, the week etc) but youre not limited to just "this month" and "last month", but you can continue to toggle between all months by clicking the arrow. i've tried with slicers and it kind of works but not as efficient or specific as i'd like it to be PLEASE TELL ME if you think that is at all possible on excel
Why should I subtract the lunch time even though its paid , can you let me know how to calculate tardiness minutes for deducting them from the hourly wage ?? you've calculated the total worked hours . but you're deducting the lunch time . I need to include the lunch time as it's a paid hour , However I wanna deduct the tardiness in the morning and after lunch though . Can you make a video on how we calculate the tardiness and deduct it from the net pay . and keep the 1 hour lunch time to be paid if you came back from lunch on time . For example work starts at 8 AM , one employee came at 8:30 , and the lunch time ends at 2 pm , the same employee came back from lunch at 2:30 PM So it means he was late for a total of 1 hour , so can you let me know how to calculate the tardiness . I really need that knowledge if you could enlighten me . Thank you and Much appreciated for the great video .
i have made payment slip and i would like to send an email to all employee, but i won't each other employee to see another salary, how can i do payroll and payment slip and employee can see only his/her own salary.
hello sir I have an excel sheet where i have data in my column as follows; company name, driver name, vehicle reg #, time in, time out, Vest no i have already done when driver comes so i take name, reg no and as soon as i take it , it fills the time in. but the time out is when the driver leaves. please, I need a code when i go to cell time out and write first letter it will appear whole time. sometimes by mistake i write on wrong driver and put the time out and i want to delete it but it does not delete, thats the problem. could u plz help me when i press delete on the cell where i want. many thanks
I was wondering do you know how to create a date and time stamp, for example: cell 1,2 and 3 cell 1 is where is would like to put the date and time stamp cell 2 is to enter the info cell 3 is to enter the info so basically i want to create a date and time stamp in cell 1, if say info was entered into cell 2 and nothing was entered into cell 3. Or visa versa.
Can someone please help me.. I am using Mac computer and using the Number rather than to regular excel. I did review the whole video at the end when i was doing the overtime sheet, it gave me an error. I did the formula of =if(B7>40,40,B7) after hit entering, it gave me an error. I could not figure out, if anyone know please help. Thanking you all in advance...
lenny amata That is ctrl+shift+~ Which will give you the decimal equivalent of the time taken of 24 hours, then multiply the formula by 24 ... =(C3-B3+F3-E3)*24
If possible, I need a little help. I have employees that work 3rd shift. For example, 10:00 PM to 08:00 AM. My total hours worked is incorrect and also consists of a negative sign in front of it.
I need to figure out how to calculate time. that does not include lunch (because they are extra work ) Then I need that time to be rounded to the next 1/4 hour ?? Also, I have put the basic formula for subtracting time and then when I put in the information the answer is ##### I thought the cell was too small but that did not work either .
I'm a Payroll representative and I find your video very helpful even now in 2018 :) thank you :)
thank you so much for this amazing video its refreshes me a lot after 15 years, it really helpful one.keep it up
I am glad that you liked it!
You are welcome! Just the sheets in the link below the video.
HAS ANYONE TOLD YOU YOU SOUND LIKE ROSS GELLER ON FRIENDS? Thanks, Ross great class!
thank you
YOUR tutorials are refreshes..
Hi Mike , Hope you are doing great .. I listened to the video it was really excellent . Am from India working as Hr , going to attend an interview on payroll .. Thank you soo much .
Best
Manju
This is a really great video! Informative and gets the work done. Great job!
This video is amazing. I have got a request. If you can please make another tutorial for the Payroll on daily basis, i mean where the overtime is calculated on daily basis instead of Weekly basis for each employee, i would really appreciate that!
Thanks in anticipation.
You are welcome!
Brilliant channel with useful info, much appreciated.
its 2021 and im here :) thank you so much for this :)
You are welcome, Ce les Te!!!
Woah, the smart side of youtube, feels nice, lulz. Good vid.
=MOD(EndTime-BeginTime,1) will work a=on any two times.
Thank you so much for these videos. I learned a lot from these.
You are welcome!
You would have to make an entirely different template to look at each daily total. I do not have a video that shows how to make a template like that. Sorry.
OMG THANK YOU! I've been stuck on the totals of my personal time all day. I couldn't make it work.
Really awesome video, thanks for sharing your skills and time
You are welcome!
Well done dude! thumbs up for this. God bless you.
+Sj Nudalo Glad the video helps! Thanks for the Thumbs Up!
Excel is really usable in payroll although MS Access is more fun and easy to use.
If there is more than one overtime rate should the IF function be used. Thank you, great video
This video is incredibly helpful. However, I'd like to know if you could show me how to do all this but to also include time taken for lunch AND more importantly when you're dealing with AM and PM time entries.
If this could be included within this video you'd get FIVE STARS!!!
Thank you so much!
=MOD(EndTime-BeginTime,1)*24 will give you hours worked.
this stuff fascinates me!! I'm such a geek lol
Thanks a lot, you've got a new subscriber :)
thanks for the fixing cell function.
Thanks for the reply!
Excellent
loving the video's man. nice work. i'm still stuck with something though. i have made my own spreadsheet for recording my own working hrs plus my wage. so what i'm stuck with is night rate, i need a formula that will kick in night rate at the night rate times and stop it when it's not night rate hrs.
You are Comprehensive instructor,Thank you. I cannot find this video work sheet on people.highline how to get it. plz
Hi Mike,
Great Video! I have a quick question:How would you adjust this model for daily overtime.
In other words, a random employee, lets call him Sam, does not work a 40 hour week. Lets say he works 35 hours.
However, during the first 2 days of the week, Sam worked 10 hours a day and the rest of the week he worked 5 hours a day.
Using this model, it would show him to have no over time when in fact, he will be owed 4 hours overtime despite the 35 hour total. How would you adjust for this?
Good easy to understand,
Fantastic.
+Le Nguyen Glad you like it!
Masterrrrr👌👌
thank you so much.
good video, thanks a lot
Click link below video
Hello, thank you for you video but i have a concern about calculating day overtime and night overtime (pay rate is different).
=MOD(end-begin,1)
Great video!! Have a quick, somewhat unrelated question: Is there a way of creating a "countdown clock" in Excel by taking the difference between the variable "=NOW()" function and a fixed future date, and getting the following formatted result: "Y:M:D:H:M:S??" Thanks for your help!!!!
How do you calculate a shift that starts at 5pm and ends at 2am? I keep getting a negative 15
do you have a video showing how to format cells for multiple different job codes? for instance if there are different rates of pay depending on the job, not just overtime or day vs night shift; i need a formula that will automatically pull data from a "job code" table or something like?
Thanks
Hey i like your videos and i have subscribed your channel too. i have a question. if a person is clock out at mid night for example a person clock in at 4pm and clock out at 8pm for a break and then clock in again at 8:30pm and clock out at 12:15am, then how can we enter a formula?? because when i tried like ur way it shows me negative value.can u help me with that???
I have a question:
I want to calculate hours for different shifts, evening, night & weekend, is that possible?''
will send you a link with Excel sheet on Google drive
thanks for your help
sir plz make a vidoe with two time break like refreshing break in and out and lunch break in and out.
Thinks sar
Mike, if am using an if statement to determine an override pay rate, can I use the time function to compare the time stamp to lets say 9:00:00? =if(start.time
Gonna teach myself to do payroll so I can put on my skills for job application. Hehe
hello really awesome video .. i was trying to create my employee payroll sheet but i don't know when i calculating total hours value showing error please advise me to in format tab which i can select and send me link for download above sheet much better for me .....Thanks Wait for your kind replay
Hello! I came across your videos, and they are very well done! I have a dilemma for my calculations, though: I have a template timesheet that I downloaded from the Internet. It is set up to take my total hours from the 1st week in the pay period and add them with the total hours from the 2nd week (I'm paid biweekly.) What I noticed is that it is doing a function where it takes the total number of hours from both weeks and multiplying it by how much I make per hour, and the result is wrong. I use the calculator on my computer to do the calculation and it doesn't match. What's the problem?
hello good day can i ask how to calculate time sheets if the working hours is less than the designated time of work like supposedly need to work 8 hours a day but the person work only for 7 hours. How to reflect it to the time sheet if overtime or under time...Thanks
Hello there not sure if anyone checks this or can help me but how would I take out a half hour break without manually putting it in like you did in this video.
I have schedule with multiple full time shifts that need a half hour taken out then part time shifts that don't.
Let me know if you can help thank you.
I love your videos! Quick thing though, I see that you do overtime base on 40 hours. Anything after 40hrs it goes to overtime. I don't want to do it base on 40hrs. I will like to do it base on 8hrs. I tried to use the if() you have(=IF(B7>40,40,B7) but changing it to 8 instead of 40 but it doesn't work. I started 830 and finished @ 18:45. Total hours 9:45 with lunch taken away. I want the cell of total hours to give me 8hrs and the overtime to give an 1:45 hrs. What am I doing wrong?
now... what if your way of calculating wages is based on time and not amount of hours per week that you are over. say from 5am to 7am is one rate and then from 7am to 1pm is another rate? thanks
how can i apply this formula?? after the same formula i mean in the same cell box or the different cell box??
Hello, I first want to say thank you for the great informative excel videos. I am a employee that likes to keep track of my own hours and I have an spreed sheet to do so. I now would like to take it one step farther and would like to calculate my yearly hours. I get regular time over time and paid double time. How do I calculate all three? In this video that I'm commenting on, you show reg and over time. ( time and half)
I hope you read this being that this video is from 2011. I am not sure how to contact you outside of this if there is such a way.
Thanks again and I'll be looking for a comment back
Alan Forbes.
I NEED HELP! PLZZ
im trying to do something similar but more specific i guess.
im a server and ive been working on an excel file to track my tips and sales percentages tip outs etc, it's very complex and im completely new to excel so it took me like two months.
i'm trying to recreate an app that i was using but doesn't track enough details.
in this app there are tabs on top for sorting the data yearly, monthly, weekly and biweekly and "all"
and then at the bottom you can toggle between all of the data with arrows, between the 2 arrows there are 2 dates based off of the filter (the corresponding dates of the month, the week etc) but youre not limited to just "this month" and "last month", but you can continue to toggle between all months by clicking the arrow.
i've tried with slicers and it kind of works but not as efficient or specific as i'd like it to be
PLEASE TELL ME if you think that is at all possible on excel
Why should I subtract the lunch time even though its paid , can you let me know how to calculate tardiness minutes for deducting them from the hourly wage ??
you've calculated the total worked hours . but you're deducting the lunch time . I need to include the lunch time as it's a paid hour , However I wanna deduct the tardiness in the morning and after lunch though .
Can you make a video on how we calculate the tardiness and deduct it from the net pay . and keep the 1 hour lunch time to be paid if you came back from lunch on time .
For example work starts at 8 AM , one employee came at 8:30 ,
and the lunch time ends at 2 pm , the same employee came back from lunch at 2:30 PM
So it means he was late for a total of 1 hour , so can you let me know how to calculate the tardiness .
I really need that knowledge if you could enlighten me .
Thank you and Much appreciated for the great video .
i have made payment slip and i would like to send an email to all employee, but i won't each other employee to see another salary, how can i do payroll and payment slip and employee can see only his/her own salary.
How do you do figure out the total hours for Saturday sir?
How would I write a formula for double time?
Hi, how do you use an IF function to check if an employee came later than an exact time e.g if mike came at 8am say early otherwise say late.Help pls
hello sir
I have an excel sheet where i have data in my column as follows;
company name, driver name, vehicle reg #, time in, time out, Vest no
i have already done when driver comes so i take name, reg no and as soon as i take it , it fills the time in. but the time out is when the driver leaves. please, I need a code when i go to cell time out and write first letter it will appear whole time. sometimes by mistake i write on wrong driver and put the time out and i want to delete it but it does not delete, thats the problem. could u plz help me when i press delete on the cell where i want. many thanks
I was wondering do you know how to create a date and time stamp, for example:
cell 1,2 and 3
cell 1 is where is would like to put the date and time stamp
cell 2 is to enter the info
cell 3 is to enter the info
so basically i want to create a date and time stamp in cell 1, if say info was entered into cell 2 and nothing was entered into cell 3. Or visa versa.
Where can I download these sheet sir?
y i can't multiply time to wage(rate par hour), please help value. it give wrong result
Sir how to calculate late timings
Can someone please help me.. I am using Mac computer and using the Number rather than to regular excel. I did review the whole video at the end when i was doing the overtime sheet, it gave me an error. I did the formula of =if(B7>40,40,B7) after hit entering, it gave me an error. I could not figure out, if anyone know please help. Thanking you all in advance...
Did you say hit F4 key to lock it??
Yes. If you have a Fn key or Fx key (Function key), then you have to use that to access the F keys at the top, like F4.
F4
or
Fx + F4
oe
Fn+F4
How do you include a time zone change with this mod formula? Im trying to change Est to central standard time.
what you up to???
I couldn't get exactly the shortcut keys to get the total working hours.. Ctrl+Shift+??= total hours
lenny amata That is ctrl+shift+~ Which will give you the decimal equivalent of the time taken of 24 hours, then multiply the formula by 24 ... =(C3-B3+F3-E3)*24
How to download this file since link doesn't work. please help
It seems to be working now.
If possible, I need a little help. I have employees that work 3rd shift. For example, 10:00 PM to 08:00 AM. My total hours worked is incorrect and also consists of a negative sign in front of it.
+Marybeth Knapp from PM to AM try this
B24 = 10:00 PM
C24 = 8:00 AM
D24= (((24-B24)+C24)-23)*24 (change your cell type to general )
Hi , Anyone clear my doubt - if worker working extra time like 7.00 pm to next day morning 3.00 am , then how to do this ?
Formula: =MOD(LaterTime-EarilerTime,1)
how can i do a payroll paystubs that include ytd
I need this too. Currently using a summary sheet by month
lol, formulas are polite...
Update my online study cases now.. I need.edly fund from companyThanks,Sergio C. Adino transfer money online webcams
I need to figure out how to calculate time. that does not include lunch (because they are extra work ) Then I need that time to be rounded to the next 1/4 hour ??
Also, I have put the basic formula for subtracting time and then when I put in the information the answer is ##### I thought the cell was too small but that did not work either .