One 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:
- In two adjacent cells, enter the beginning time value and the time that is 30 minutes later than the beginning time value.
- 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:
- Use a Formula to subtract the Start Time from the End Time.
- 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)
