Excel 2010 Business Math 44: Payroll Time Sheets, IF Function For Overtime &Gross Pay Calculations

Download Excel File: people.highline.edu/mgirvin/A...
This is a Business Mathematics Class (Busn Math 135) taught by Mike excelisfun Girvin at Highline Community College.
In this video learn how to calculate:
1. Time Number Format and Time Math
2. Complete Payroll Time Sheets in Excel for Employees
3. Learn how to do a Sheet Reference In Excel Formulas
4. IF Function for Overtime Calculation
5. Calculate Gross Pay from Time Sheets
Related videos:
Excel Magic Trick 501: Excel Time Format & Calculations (10 Examples)
Excel Magic Trick 286: MOD function & Time Calculations (Time For Night Shift, or Negative Time)
Excel Magic Trick 727: Calculate Hours Worked Night or Day Shift With Break For Lunch

[@MsLoredana34]

I'm a Payroll representative and I find your video very helpful even now in 2018 :) thank you :)

[@elisasunga8038]

thank you so much for this amazing video its refreshes me a lot after 15 years, it really helpful one.keep it up

[@excelisfun]

I am glad that you liked it!

[@Evanhcpa]

This is a really great video! Informative and gets the work done. Great job!

[@UbuntuXII]

Brilliant channel with useful info, much appreciated.

[@DegenerateToo]

HAS ANYONE TOLD YOU YOU SOUND LIKE ROSS GELLER ON FRIENDS? Thanks, Ross great class!

[@excelisfun]

You are welcome! Just the sheets in the link below the video.

[@dondhoncasubuan6198]

thank you YOUR tutorials are refreshes..

[@Princessjdarling]

OMG THANK YOU! I've been stuck on the totals of my personal time all day. I couldn't make it work.

[@excelisfun]

You are welcome!

[@princessrodrigo8325]

its 2021 and im here :) thank you so much for this :)

[@excelisfun]

You are welcome, Ce les Te!!!

[@materialgurl0718]

this stuff fascinates me!! I'm such a geek lol

[@mikeyzhu9195]

Thank you so much for these videos. I learned a lot from these.

[@excelisfun]

You are welcome!

[@michaellarowchellmendiola3341]

Thanks a lot, you've got a new subscriber :)

[@BossWolfen]

Woah, the smart side of youtube, feels nice, lulz. Good vid.

[@manjadi143]

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

[@JesterzPlauge]

Thanks for the reply!

[@sjnudalo2561]

Well done dude! thumbs up for this. God bless you.

[@excelisfun]

+Sj Nudalo Glad the video helps! Thanks for the Thumbs Up!

[@excelisfun]

=MOD(EndTime-BeginTime,1) will work a=on any two times.

[@excelisfun]

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.

[@mfrank5440]

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.

[@LeNguyen-im8dm]

Fantastic.

[@excelisfun]

+Le Nguyen Glad you like it!

[@LAMLAM-uf2ho]

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!

[@RafiqKhan-xp2ic]

Good easy to understand,

[@xiaoluyang1978]

thanks for the fixing cell function.

[@MrNotubo]

Excel is really usable in payroll although MS Access is more fun and easy to use.

[@sijonakkara]

good video, thanks a lot

[@drdoller07]

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.

[@missjcj222]

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?

[@dansmith2711]

Really awesome video, thanks for sharing your skills and time

[@excelisfun]

You are welcome!

[@chowman007]

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

[@bjgibbon]

If there is more than one overtime rate should the IF function be used. Thank you, great video

[@maimaichristina]

thank you so much.

[@TPBass1224]

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!!!!

[@JesterzPlauge]

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?

[@excelisfun]

=MOD(EndTime-BeginTime,1)*24 will give you hours worked.

[@mohameddoumbia729]

Hello, thank you for you video but i have a concern about calculating day overtime and night overtime (pay rate is different).

[@jayephgee]

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?

[@gadgetsworld8735]

Excellent

[@gerrylfedilo688]

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

[@MusthafaKottopadan]

You are Comprehensive instructor,Thank you. I cannot find this video work sheet on people.highline how to get it. plz

[@wick515]

How do you calculate a shift that starts at 5pm and ends at 2am? I keep getting a negative 15

[@excelisfun]

=MOD(end-begin,1)

[@expeditorssecurity7295]

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

[@spidytube1]

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

[@TheLABound123]

Gonna teach myself to do payroll so I can put on my skills for job application. Hehe

[@johnwatkins39]

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.

[@alanforbes2112]

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.

[@futuresuperstar4life]

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

[@aneeshparippally]

Masterrrrr👌👌

[@atifiqbal5310]

sir plz make a vidoe with two time break like refreshing break in and out and lunch break in and out.

[@ShawnJames76]

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.

[@alinnarra73]

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?

[@minhasp1]

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...

[@shravangaddam8459]

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

[@sinnakhonesouksouvanh9982]

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.

[@mrsPitusa169]

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 .

[@UbuntuXII]

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

[@philiswhitsitt4630]

How do you include a time zone change with this mod formula? Im trying to change Est to central standard time.

[@truesolution6069]

what you up to???

[@jon11790]

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???

[@royalnass1029]

How do you do figure out the total hours for Saturday sir?

[@excelisfun]

Click link below video

[@boulala21]

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

[@kotreshah1583]

Thanks

[@jon11790]

how can i apply this formula?? after the same formula i mean in the same cell box or the different cell box??

[@jamalsaid8762]

Thinks sar

[@mohammadhammouri1927]

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 .

[@syedimran9406]

Sir how to calculate late timings

[@mikerobertastrode4642]

How would I write a formula for double time?

[@dharmveermeena6329]

y i can't multiply time to wage(rate par hour), please help value. it give wrong result

[@teresoacosta]

Where can I download these sheet sir?

[@lennyamata0987]

I couldn't get exactly the shortcut keys to get the total working hours.. Ctrl+Shift+??= total hours

[@axdesho]

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

[@marybethknapp2720]

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.

[@housokheng5115]

+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 )

[@ranjithveeraiyan5748]

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 ?

[@excelisfun]

Formula: =MOD(LaterTime-EarilerTime,1)

[@robertyates6521]

how can i do a payroll paystubs that include ytd

[@bjgibbon]

I need this too. Currently using a summary sheet by month

[@4everlakeisha272]

Did you say hit F4 key to lock it??

[@excelisfun]

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

[@zameerpathan431]

How to download this file since link doesn't work. please help

[@excelisfun]

It seems to be working now.

[@sergiojew4828]

Update my online study cases now.. I need.edly fund from companyThanks,Sergio C. Adino transfer money online webcams

[@krn14242]

lol, formulas are polite...