Home Archives for Formatting Cells with Time Calculations
July 16, 2012 1 Comment

How to Create a Series of 30 Minute Time Intervals in Excel

How Excel Stores TimeOne of my readers tracks data points at 30 minute (half-hour) periods throughout the day. He asked me for some help in streamlining this process.

AutoFill the Step-value for a Series

You can use AutoFill in Excel to create this series. Here are the steps to take:

  1. In two adjacent cells, enter the beginning time value and the time that is 30 minutes later than the beginning time value.
  2. Select BOTH cells and AutoFill the cells in the range that you want to “populate.”

Calculate Decimal Number that Represents 30 Minutes

You can calculate the numeric value for a half-hour (30 minute) period:

  1. Use a Formula to subtract the Start Time from the End Time.
  2. Format the result as a Number with 6 Decimal Places.

In this case, the result is: 0.020833 which you can use as a Constant Value in a Formula.

Keyboard Shortcut to Enter the Current Time

Ctrl + Shift + : (Colon) will “time stamp” a cell.

How Excel Stores Time

While you can Format Time in a variety of ways, Excel must be able to recognize and store Time entries as Numeric entries! This means that correctly entered times align to the right-side of the cell. Time values are stored as a “decimal part of one” day. For example, 12:00 PM (Noon) is stored as 0.50 – 50% of the day has elapsed

Shop at My Secure Online Website

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to view all of the resources that I offer you to improve your Excel skills.

Watch Tutorial in High Definition

Click on this link to watch this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

How to Create a Series of 30 Minute Intervals in Excel [ 6:22 ] Play Now | Play in Popup | Download (2689)
Link to this post!
Filed Under: Excel Basics, Excel Training, Keyboard Shortcuts, Tips and Time Savers Tagged: , , , , , , , ,
January 26, 2011 2 Comments

How to Calculate Overtime and Standard Hours Worked on a Time Card in Excel

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

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)
Link to this post!
Filed Under: Excel Date and Time Functions, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , ,