One of my viewers asked me to show him how to calculate the number of hours worked each day – separated by standard hours and by overtime hours. He, like many Excel users, is frustrated when performing calculations involving time. They create a standard formula, but the results are not what they were expecting. In most cases, this is because of the need to FORMAT the cells that contain calculations to accommodate Excel’s logic.
Excel Time Card
Time Functions and Formats
In this lesson, I demonstrate the following functions and formats:
- The =TIME() Function requires three arguments (Hours, Minutes, Seconds). In this example, I use =TIME(8,0,0) in a cell to represent the Standard Hours Worked each day – i.e. 8 hours.
- The CUSTOM Format h:mm to format the cells that contain the results of time calculations (Total Hours Worked each day, Regular and Overtime Hours Worked each day).
- The CUSTOM Format [h]:mm to format the cells that contain the results of SUM() for the Total Hours, Regular and Overtime Hours Worked each WEEK – i.e. Hours that exceed 24 hours
- The Formula =SUM(Overtime Hours Worked this week) * 24 to gives me the NUMBER equivalent – Remember to format this cell as a NUMBER – not as [h]:mm, so that you can multiply it by the cell that contains the Hourly Rate that you pay for either Regular Hours or Overtime Hours.
How Time is Stored vs. How Time is Displayed
Remember to distinguish between what Excel Stores as a calculation and how Excel Displays the result of a calculation. This is especially important with TIME and DATE Calculations in Excel.
You can view this lesson in High Defintion, Full Screen Mode on my YouTube Channel – DannyRocksExcels – by clicking this link.
I invite you to subscribe to my free Video Podcast – “Danny Rocks Tips and Timesavers”- at the iTune Store by clicking here.
Vist my Online Shopping Site
My DVD-ROM, “The 50 Best Tips for Excel 2007” is on sale at my Online Bookstore – http://shop.thecompanyrocks.com – Click here to open a secure shopping cart.
Learn how to “Master Excel in Minutes – Not Months”
How to Calculate Overtime and Standard Hours Worked on a Time Card in Excel [ 10:09 ] Play Now | Play in Popup | Download (1102)
I need to figure out overtime hours for my company. Currently, the employee enters into Excel: Start time, End time and it calculates Total hours. My struggle is the evenings and weekend, since the overtime is based on before 6 am and after 6 pm week days AND between 6 pm Friday to 6 am Monday. What formula can I use? Do I need to have overtime in a different line than regular hours? Thanks for any help.
Jan
Hi Jan –
To solve your problem, you will need to use these functions: IF(), AND(), OR() and WEEKDAY() in a combination of formulas. You should also be sure to have a “Date” for the work hours. so that we can figure out which day of the week (for the WEEKDAY() function).
I get asked this question frequently. Give me a few days and I will create a video lesson that demonstrates how to set this up for you.
Danny Rocks
The Company Rocks