Formula for putting overtime in seprate cell

Occasional Visitor

Im new and trying to put in my husbands hours and having anything over 40hrs go into a separate cell so I can have it calculate his pay for OT. I have the cells that I put his hours in, a cell that calulates all of his hours, but I have to manually put his OT hours in the separate cell right now because I have yet to figure out what formula I would use to put anything over 40hrs into the OT cell. 


Im doing this as a project to learn more about excel and be able to make my own workbooks. So far this is the only workbook I have created on my own, and this is the only thing Im stuck on. I have already figured out taking out takes and other deductions. Calculated YTD hours and pay. This is literally the only thing Im stuck on. I will take any help anyone can give and use it to learn further, but I need to see the working formula for this one I think. I think maybe Im just overthinking it. Right now Its a very simple workbook.


Thanks so much for anyones help


1 Reply
Hi Abeale!

To count your hours in one cell you have to change the cell formatting. Select the cell, take the shortcut CTRL 1 ( on alphanumeric keyboard, not on numeric block!) and now you’re on the formatting dialogue. There you can select date and time and search then for a format called „37:30:55“. This is the right format to show the hours beyond 24 hours. Click okay. Then take the sum function to sum up the different cells for your several hours.

Have a nice Weekend. Greets, Eva
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies