Calculate Hours Worked Overnight in Excel - Tutorial

Excel tutorial on how to calculate hours worked overnight in Excel. Calculating the hours worked when you clock out the next day is a bit tricky. We'll go over a formula you can use to help you sum hours worked in a night shift.
It's important to note that Excel sees time as a decimal between 0 and .99, each number representing a fraction of a 24-hour period. So when you need to subtract PM from AM in Excel, the result is a negative number. And Excel does not process negative numbers as time and so displays an error.
In this tutorial, we follow an example of tracking hours worked. We go over a formula you can use to calculate hours worked in an overnight shift, how to convert the hours to a regular number, and then use that to calculate pay in Excel based on the hours worked.
As promised, if you want to learn more about the math behind MOD, take a look at this explanation from Khan Academy: bit.ly/2SynwoP
If you liked this, make sure to get your FREE pdf list of top 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

[@_Aidelweiss_]

I been looking hours for this formula. thank you

[@YAcostaSolutions]

That’s great! I’m so glad this helped you out 🙂

[@TheModernGlitch]

Thanks so much for this video! I was really having trouble with this formula!

[@YAcostaSolutions]

Glad it was helpful! Thank you 🙂

[@harrydoolabh6071]

Thank you you make it so simple and easy to understand

[@YAcostaSolutions]

Happy to help, Harry! Glad you liked this. Thanks 🙂

[@hanumanjipankaj]

Thanks for the help

[@YAcostaSolutions]

Thanks. Happy this helped you 🙂

[@anilrawat317]

Thank you so much for this video to convert total hours to number

[@YAcostaSolutions]

Thank you! I'm so glad you liked this 🙂

[@italinashane8400]

Thanks I learned it in a matter of minutes. Great lesson.

[@YAcostaSolutions]

That's great, Italina Shane! Happy this was helpful for you 🙂

[@edwinotieno9470]

You are underrated . You deserve more subscribers and views. Very helpful clip. Thanks a lot

[@YAcostaSolutions]

Thank you so much, Edwin 😊 That’s very kind of you

[@shahzadsami5613]

Agreed 1000%

[@soumiamoujane3695]

Thanks very helpful

[@YAcostaSolutions]

Great! Glad this was helpful for you and thanks 🙂

[@rogerwillis7692]

I was wondering when you were going to add this info! Thanks!

[@YAcostaSolutions]

Thanks, Roger! I hope you found this helpful. I agree, this topic was past due! Schedule-wise it just took a little bit to get there. If there's ever anything you'd like to see covered, please do send a note 🙂 Thanks

[@346125879]

Thank you so much

[@YAcostaSolutions]

Thanks! I’m glad this helped you 🙂

[@titan2793]

you are awesome. thanks.

[@YAcostaSolutions]

Thanks so much! You’re pretty awesome too 🙂

[@bupbekodau]

thank you

[@YAcostaSolutions]

Thank YOU! Happy you liked this 🙂

[@Coquitlam2024]

Hello Acosta, thanks for this but I have a question

[@engtap2850]

Nice video.

[@daniel.shalome]

Thanks very helpful to track the amount of sleep im getting

[@YAcostaSolutions]

Lol! I'm happy you found helpful. Thanks for commenting 🙂

[@Hi-sy8ly]

Hi
How to maintain same date after midnight ( do not change to next day after 12 am)

[@anonymousperson4363]

Would this work if you start at 1 pm and finish at 2 pm the next day?

[@cattac6561]

If I work after midnight is 1.5 rate, start time 10pm, end time 5 am which formula for time calculation the best😮

[@YAcostaSolutions]

Hi. You could try creating an IF function that would reference a different pay rate based on the time. Or you could create different line items on your time sheet (one for regular hours and one for overtime hours) then sum your regular and overtime pay together. Hope this helps and thank you!

[@cduanetech4491]

help please. .formula on computing hours work .. sample 3:00pm - 4:30 . equivalent hours is 1.5 ..tnx a lot