How to Track Hours Worked in Excel + How to Calculate Pay in Excel - Tutorial ⏰💰
ฝัง
- เผยแพร่เมื่อ 11 ก.ย. 2024
- In this tutorial, we go over how to track hours worked in Excel. We’ll also go over how to calculate pay in Excel for the hours worked.
Useful for freelancers, independent contractors and anyone that needs to track hours for a job, track their daily wage or create a timesheet.
We also go over a simple way to convert the total hours from time format to a regular number that can be more easily used in calculations.
And, to try a more convenient and simpler way to track hours worked by using a cloud-based time clock system for free, go to: www.ontheclock...
Click here if you would like to read our step-by-step article on this: yacostasolutio...
And make sure to get your FREE pdf list of favorite Excel keyboard shortcuts: yacostasolutio...
*FYI:
This tutorial was created using the desktop version of Excel in Microsoft 365. You can upgrade to this version here: yacostasolutio...
The screen recording + editing was done using Techsmith Camtasia. You can check it out here: yacostasolutio...
Thanks for watching this Microsoft Excel tutorial and, don’t forget, if you like this video, please “Like” and “Share” with your friends - it really helps us out 😊
To catch our Excel tutorials as they come out, subscribe to this channel by clicking: yacostasolutio...
Thanks!
*DISCLAIMER: This video description contains affiliate links, which means that if you click on one of the product links, I’ll receive a small commission. As an Amazon Associate I earn from qualifying purchases. This helps support the channel and allows us to continue to make tutorials like this. Thank you for your support!
#YAcostaTutorials
Very helpful!!! I watched like 3 videos and they did not put the *24, which was messing up my hours. This really helped!!! Thank you!
That's great, Vivid Rose! I'm happy this was helpful for you.
Also, in case you need it, I have another tutorial on tracking hours worked overnight. It's a little bit different and I missed this part the first time around and it took me a bit to get to it: th-cam.com/video/5GbAf_CeDZk/w-d-xo.html
Thanks 🙂
THANK YOOOOOOOOOOOOUUUUUUUUUUUUUUUUUUUUUUUUUU!!! No 11 minute video showing a bunch of codes.
Thanks so much, AizenSamaKing X! I'm happy this was able to help you 🙂
This is the most helpful sheet videos i've looked into calculating hours and pay. Easy to understand! Thank you!
Thank you so much for the compliment, Lady Boss 🙂 I'm happy this was helpful for you!
Gonna start my first day on my first job tommorw, ty for teaching me this.
That’s great! Hope you love your new job and thanks for stopping by 🙂
@@YAcostaSolutions ty
Wow! Thank you so much for this video! I haven't had to use excel since high school but was in need of doing some work in excel and decided to come to TH-cam. Your video was the second one I watched and I'm so glad I picked it. I went back and forth and was able to follow along to the T! Great tutorial and very informational.
That's great!! I'm so happy this helped you out 🙂
Excellent video!!!! Thank you!!! You were VERY clear and got right to the point, which is exactly what I like :D
Thank you, loppy lop! Happy to help 🙂
Very helpful for people who are just beginning Excel formulas.
Thank you. I'm glad you found it helpful 🙂
Thank you. Your great. I love that your clear and fast.
Thanks so much! I'm happy you liked this 🙂
This was a quick easy and smart way to do payroll and within a matter of minuets I understood the basics
Overall excellent tutorial. After going through several tutorials, I found this one and it was very easy to follow. Since I am fairly new to this type of instruction, I am not sure what happens if I subscribe. Is there a fee or do I get inundated with emails?
Is there a way of taking this, and having it total up day by day to give the overall monthly hours worked and pay on separate columns?
Thank you so much for a very clear informative right to the point tutorial, i was so frustrated watching other tutorial until i found this one A++++
You're very welcome! I'm happy this helped you 🙂
This was so great - easy to understand. Thank you!
Thanks, THEGT16! I'm happy you liked it 🙂
Thanks a lot! Perfect explanation' short and clear
Thanks so much, Ricardo. I’m happy this helped you 🙂
Thank you for your tutorial.
Q. What do I put if one of the days of the week was a paid Public Holiday(7.6 hrs)?
I know the hours the accountant calculates is 7.6hrs. So if you had 1 week holiday then you do 7.6 x 5 = 38hrs per week.
Thank you very much. This really helped.
Awesome! Happy this was able to help you 🙂
Thank you so much. This has been really helpful for my own payroll
That's awesome, TheClaudos! Thanks for sharing 😀😀😀
Also, in case you're interested, I recently did a similar tutorial where we calculate PTO: th-cam.com/video/usrfLo4247M/w-d-xo.html
THANK YOU❤️❤️❤️
You're so welcome! I'm happy this helped you 🙂
Thank you very much easyly understanding
Thank you, Ja Gadeesh! I'm happy you found this helpful 🙂
Thanks so much this was really helpful
Thanks so much, Leah. I'm happy this was helpful for you 🙂
Thank you! Solved my problem.
Glad it helped! Thanks so much 🙂
excellent video, very detailed and worked perfect for us!!!
Awesome! Thanks for sharing this with us 🙂
It helped a lot, Thank you
Thanks! I'm so happy this helped you 🙂
This is so awesome! How would you calculate any overtime? Im more wondering on what formula to use
Hi, Hazel. Thanks so much for watching and commenting. I just did a tutorial on a couple of ways to calculate overtime. I hope this is helpful for what you're working on. Thanks!
th-cam.com/video/7mqE9KU4fuw/w-d-xo.html
Thank you very much. This was easy to understand and gave me exactly what I was looking for.
That's great!!!! I really appreciate you sharing 🙂
My god that was helpful.Thank you so much.
Awesome, Phoenix! I'm glad this helped you 🙂
thanks!
You're welcome! I'm glad you liked this 🙂
You are the BEST!!Thank you!!
Thank you for the compliment, Enrique ☺️ Happy you liked this.
Not working.....
Very helpful.
Great! I’m happy you found it helpful and thank you for sharing with me 😊
THANK YOU!
Thank you, Zeina! I’m glad you liked this 🙂
Excellent tutorial, Thank you so much!
Thanks so much 🙂
Hi, I love your tutorials but I'm having trouble getting the calculation in Excel to come out correctly. Our time is calculated on a 6 minute conversion to decimal in tenths, so if you clock in from 12:00 - 12:05 = 0 minutes, 12:06-12:11 = .1, 12:12 - 12:17 = .2, 12:18-12:23=.3, etc. For example: Clock IN at 9:02 PM and Out at 1:44 AM = 4.7 hours worked or Clock IN at 9:47 PM and Clock Out at 2:14 AM = 4.5 hours worked. Can you help me please? Thank you
Fandidilytastic :0)
Thank you so much! I’m glad you liked this 🙂
You are incredible! - thank you for your amazing content
What a generous compliment! Thanks so much. I’m glad you liked this 🙂
Hi,
My start time was 2:56 PM, End time 10:04 PM. Break starts at 6:14 PM and break ends at 6:48 PM. My question is, as per the company, my total work hours are 6:34 but when I calculated, it is coming as 6:57.
Could you please help me where I went wrong?
Very helpful!
Thank you, Thea 🙂 I’m happy you found it helpful!
Yes I did! Thanks so much!
thanks
Thank you! I'm happy you liked this 🙂
Quick + Easy! Thank you!
Happy this was helpful for you, OTEETS! And thank you 🙂
man thank you dunno why it works on your video i no joke tried it on all other videos on youtube something similar.... i wanna keep my hours in check and i just had that VALUE ERROR SIGN i tried fixing it didnt do anything couldnt do anything more.
realy good! thank you
Thank you, doronkra! I really appreciate the comment 🙂
Please help so i did this but how come that the hours when i manually calculate them it shows dofferently?
me too :(
Great..!!
thanks
Thank you so much! Happy you liked this 🙂
What if there's a split? 12:00 - 16:00 and then 19:00 - 00:00 . I really need help on this, please..
Hi, Malai. You could try a couple of things. You could do 2 line items for the same day where the first starts at 12 PM and ends at 4 PM. The other starts at 7 PM until 12 AM.
You could also start at 12 PM, end at 12 AM and note a 3 hour break.
Also, to calculate the hours, since it ends at midnight, you'l want to use the MOD function. It'll look something like this (but with your cell references instead of words):
(MOD(End Time-Start Time,1)-(Any Breaks))*24
I hope this helps. Please let me know if you have more questions. Thanks
Please PLEASE tell me you can help!
I desperately need to be able to add a second frozen title row at the top that has "tabs" that will filter my table/column one (if it were dates) by weekly, pay period, monthly, yearly and all. Maybe have to date ranges in a block at the bottom and a way to scroll through them to the next selection of dates. Is this at all possible?!?!
Hi, futuresuperstar4life. You could try converting your data into a Table (Ctrl+T or Home tab / Styles section / then click Format As Table and select a design). Once it's a Table, you can insert a Slicer. Slicers work like interactive buttons in Excel to filter data.
Place your cursor within your Table data and you'll find the option to add a Slicer within the Table Design tab in your ribbon.
We have a tutorial on Slicers that shows them used with Pivot Tables, but they work the same with regular Tables. It's a short tutorial, only like 4 minutes: th-cam.com/video/LzHqAxgiLVA/w-d-xo.html
Hope this is helpful for you. Please let me know if you have more questions or need anything else.
@@YAcostaSolutions I'm using excel on my phone... and thereafter nothing about slicers up here...
Thank you!
Thank you, Sara. I’m glad you found it helpful 🙂
Finally a excel video with no ear rape
Thanks, Dre Plays 🙂
Like this tutorial? Make sure to grab your FREE pdf list of top Excel keyboard shortcuts: yacostasolutions.com/newsletter/
nice
Thanks, Roblan. I'm happy you liked this 🙂
I work 6pm to 6:30 am, this function is not working. help? Also once that is figured out, I would like a total at the bottom of hours and pay (if possible on a semi monthly or monthly basis)
Thank you
Thank you, Aman. I'm happy you enjoyed this 🙂
What if don’t want to add AM and PM each value?
Its not working for night shifts if you work from night to morning of another day
Hi very useful! how can I manage amount of time instead? for example I use an app that counts the hours and minutes spent on a task, so I need then to put this amount and calculate the compensation per/hour. But the problem is that I can't convert it into the decimal format because I use a "duration" format in excel! thank you!
Hi. You should be able to do the conversion in the cell where you're calculating the compensation.
For example, in the tutorial, we multiplied: total hours * hourly pay to get the total pay.
You could instead multiply: total hours * 24 * hourly pay in the cell where you're displaying the total pay.
Does this help with your question? Please let me know. Thanks
I only track weekly hours worked for multiple employees in a table in my cost spreadsheets. Now their pay rates are changing...or some are, some are staying the same and all happening at different times. Wondered if you had any advice for that?
How do I find the amount of hours worked for someone who has worked for 13 years for 2-3 times a week for 60 minutes and has worked 1411 days in total with 4.5 minutes break in between work.
greet job thank you so much
but I have an issue for example I start work at 11:05 AM and finished at 08:00 PM I got work hour 8:92 how can I fix it?????
Hi, Katakit. Your result is actually 8.92 hours, not 8:92. To see the total in hours in time format (not decimal), don't multiply by 24. Also, if you want your result in time format, you'll want to use the same formatting as for the Breaks column (custom formatting / then use h:mm). I hope this helps. Please let me know if you have more questions.
I need to know how to enter a formula that will calculate total time with OT and vacation/holiday pay.
For example: Person is paid biweekly. Week 1, works 42 hours. Week 2, works 38.5 hours and uses 4 hours vacation time. Their total hours should be 78.5 hours regular time, 2 hours OT and 4 hours vacation. So 82.5 hours paid at regular time rate and 2 hours paid at OT rate. I can't figure out how to have the spreadsheet only calculate the OT hours based off of 2 hours and not include the additional 2.5 vacation time at OT rate. Can you help??
Hi, Megan. It's not hard to do, but a bit convoluted to explain in a quick response. However, I had started including OT calculation in my PTO tutorial, but then felt it was going off topic and making the video too long, so I scrapped it. I may still do it as a stand alone tutorial later. Anyways, what I'm trying to say is that I still have the file and can clean it up some and send to you, if you like. If you're interested, please send me a note at: yacostasolutions.com/contact/ Thanks
this is of great help thank you. The only thing i am having trouble with is the break ... I have 10:23 am to 8:05 pm with a 1 hr break, when I sum it up it gives me 8.55, when i clock out from work it gives me a receipt of the hours i worked that day for them it shows 8.36 . am I doing something wrong ?
Hi, Maria. Is the receipt generated by a time clock from your employer? If so, I wonder if they’re rounding time? I once had an employer who, depending on when someone clocked in, would round up or down to, I think, the closest 15 minutes. Anyways, are you able to check if the clock in/out times used in your time clock receipt are the same as what you used in your spreadsheet? Please let me know. Thanks
yes it on a tablet, not sure what it is called. this is how it looks In: 10:32 am , out : 3:26 pm ( total hours : 4.54) , in 4:23 pm , out : 8:05 pm ( total hours 3.42) then it gave me the total of8.36
🤦♀️ Hi. I should have realized this yesterday. They’re showing you the time worked in “time” format. In this tutorial, we converted it to a decimal to make it easier to calculate the pay.
So, in your Total Hours column, don’t multiply by 24. Then use the same formatting as in the Breaks column. You should get the same results as on your receipt.
Hope this helps. Please do let me know if you need anything else.
@@YAcostaSolutions thank you very much for your help
Sure. Happy to help!
What if you have different pay rates for different times of the day ?
Hi
How do you calculate if the ‘Pay Rate’ changes depending on the day and time eg Weekend higher Pay Rate and Evening higher Pay Rate ?
Hi, Cleo. This is a bit tricky. Does the pay rate depend on the start time or end time? You may want to try creating a table with the weekday, hours and pay then using INDEX / MATCH to look up the pay rate.
Let's say that, along the top, you do a column header for each hour of the day (12:00 AM, 1:00 AM, 2:00 AM, etc).
On the headers along the side, you could use the day number for each day of the week (Sunday = 1, Monday = 2, etc).
And the hourly wage would be the data in the middle of the table. So, the cell where each day and hour intersect would represent the hourly wage for that day and time.
Then, in the log (from this tutorial) you may want to add another column in for the day number of the week - you can use the WEEKDAY function to reflect the day number of the date that you worked (this returns the number of the weekday like Sunday = 1, for example). This is what you'll use to reference the day in the INDEX / MATCH.
I would also recommend creating a drop-down for selecting the hour in the log. I've been playing with it for a bit and, for this to work, it's important that the times be formatted exactly the same and it's easy for them to not be.
The INDEX / MATCH formula would be placed where the Pay Rate is currently.
Here are some tutorials I think can also help:
INDEX / MATCH tutorial: th-cam.com/video/XPtt12ioWTk/w-d-xo.html
Drop Down List Tutorial: th-cam.com/video/4KriAdA-b5c/w-d-xo.html
I hope this helps...it's a lot so please let me know if it seems convoluted.
Thanks
Thank you very much, I literally just woke up from a night shift so it was a lovely surprise to see your message as it’s been on my mind! I will experiment with what you suggested.
The thing is, the pay rate changes depending on start time AND end time.
Example :
Mon-Fri 08:00-19:00 Pay Rate = £10
Mon-Sat 19:00-08:00 Pay Rate = £15
W/E Sat-Mon 08:00-08:00 Pay Rate = £20
You're so kind 🙂 Are there set blocks of time or are the time blocks always different?
Because it’s shit work the time blocks are all different, sometimes 8am - 8pm or 2.45pm - 10.00pm, 9.45pm - 10.00am (night shift)
🙈🙊Oops! I meant ‘Shift’ work...Freudian slip?🤔🤣🤣🤣
Having issues with the equations. Keeps giving me 12.00 when I multiply
Hi yacosta I am doing my placement and can I apply this on 7 1/2 hour to make 120 hours
If my clock in time is 9 am and clock out time is 7 am next day.. It is showing -2 value.. Can u help me?
Hi, Adithya. Yes, if you work past midnight you would need to use the MOD function. This tutorial goes over how to do this: th-cam.com/video/5GbAf_CeDZk/w-d-xo.html
Thanks and hope this is helpful for you.
@@YAcostaSolutions u r a life saver.. Thanks
Loved it but still don't get exact value 😢
This doesn’t work. I get a value error. When I use the formula to calculate total hours
I need help how do I make the whole column the same formula without typing it row by row??
Hello. Here's how you can do this:
1. Type the formula in the first cell of the column
2. Place your cursor back in the cell where you just typed the formula
3. Press Ctrl+C on your keyboard to copy the formula
4. While holding down the Shift key, arrow over until you've selected the number of cells in the column that you want to use your formula in
5. Press either Enter or Ctrl+V to paste the formula to all the cells you've selected
Hope this helps and thanks!
I clocked in at 10:11 am and out at 2:15 how do I figure hours worked using the computer calculator? I don't have excel on my home computer
Hi, bear teddy. To do it on a regular calculator, first, figure out the difference in time, it's easier to calculate if you do it in military time. For example, I'm guessing that 2:15 is in the afternoon and 10:11 was in the morning. So, you'd subtract 10 hrs, 11 minutes from 14 hrs, 15 minutes. In this case, you worked 4 hours and 4 minutes. This is the same as 244 minutes (4*60 + the 4 minutes).
Then, to calculate the pay, divide 244/60 to get the decimal. In this case, that would be 4.07. Then, multiply your hourly wage * 4.07.
I hope this helps! As a side note, if you're unable to get Excel, you can try Google Sheets. It works very similarly to Excel and is free with a Google Drive (which is also free).
Thank you, yes this did help and I should have put the am / pm
Awesome!
Wow
What about the paid days off?
Hello, Hager! As it happens, we're currently working on a tutorial to go over how you can track vacation days. I expect it should be out shortly after the holidays. In the meantime, is there a specific paid days off question I can try and help you with?
@@YAcostaSolutions first thank you for replying ❤️, actually i made the same sheet as in the video and i stoped on the week ends and didn't what to do as we count the salary for 30 days
Hi, Hager. I’m not following what you mean by “we count the salary for 30 days.” Would you be able to describe for me so that I can try and help? Thanks
How do i add up all hours?
Hi, Betzy. Once you convert the hours to regular numbers, you can just sum them. Please let me know if you’re having any issues with this.
Please zoom little bit. Can't catch it. Thanks
Hi, Jack. I apologize that it's hard to see the steps in the tutorial. I've been trying to improve this in some of my newer tutorials. It was mostly due to inexperience in editing. Unfortunately, this isn't something I can edit once the video is live.
However, we've been working on building out a website to complement the tutorials in our YT channel. I noticed that you had also left a comment in the tutorial on how to Make a Goal Thermometer. The steps for that (along with images and animations for each step) were recently added to the website. You can find it at: yacostasolutions.com/goal-thermometer-in-excel/
Once we have the step by step instructions on the website for the Tracking Hours Worked tutorial, I'll make sure to come back and share that with you as well.
Again, sorry for the inconvenience. Thanks
Hi, Marcus. Just following up on this. Wanted to let you know that we've (finally!) published step by step instructions for the how to track hours worked in Excel tutorial.
In case you're still interested, here is the link to the article: yacostasolutions.com/track-hours-calculate-pay-excel/
Thanks! And sorry this took such a long time.
What if start on 2.30 PM and end on 12.30 AM the next day?
Hi, Doge. To calculate hours worked overnight, you need to append your 'total hours' calculation with the MOD function and use 1 as the divisor. It would look something like this: =MOD((End Time - Start Time),1)-Breaks
We have a different tutorial to help with this. Take a look: th-cam.com/video/5GbAf_CeDZk/w-d-xo.html
@@YAcostaSolutions thank you so much for the help
You're very welcome 🙂
I work 6pm-6:30am. I cant seem to get this to work
What if there's Vacation Pay?
Hi, Winston. You could do a line item for the vacation pay. For example, if 7-18-19 is your vacation day and you should be paid for 8 hours of vacation, you could note a standard Start Time and End Time that would equal an 8 hour day (9am to 5pm, for example). Then apply a corresponding cost per hour. Perhaps add a "Comments" column so that you could note that it was vacation pay. Just a thought. Hope this helps and thank you.
When i add =(D3-C3-E3)*24 in total hours #VALUE! appears in that cell. Can you please help me with that?
Hi, BK-GAMING. Sorry for the late response, not sure why your comment was trapped with my "held for review" comments and I hadn't looked there in a while. Anyways, it looks like the formula you're using is correct (End Time - Start Time - Breaks)*24. Make sure to double-check your time formatting. Please let me know if it's still glitchy for you. Thanks
Dont type AM for example if start time is 8 am type it as 8:00 and it will automatically add the AM
👍.
Thanks, Biplab 🙂
salary not coming exact amount.
Hello, Bijo Jacob. Can you double check that your total hours for each day are the correct amount? I wonder if that’s where the disconnect is? The salary is the result of the total hours multiplied by the hourly pay for each hour. If you could send me a little more information I can see how I can help you better. Thanks
👍👍👍
Thank you, Karanvir Singh Birdi 🙂
@@YAcostaSolutions mention not dear sis
Shift start 18:00 Shift end 04:00. total hours #########. thatths what i get :(\
My Total hours is not coming up right.
Hi, Ari Network. Can you share a little more about how the total is not coming out right? Does the formatting look off? Are you calculating overnight hours worked? Something else? Thanks
8AM to 5PM plus break 30 min.... is 8.50 total? is very wrong
that is correct, 8 1/2 hours
Good formula, wrong math. Especially that it's money we're talking about.