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)
