In my experience, many Excel users get “frustrated” when trying to enter and calculate Time Intervals. For example, setting up a schedule for meetings that begin and end in 45 minute intervals. After you watch this video tutorial, you will learn how easy it is to set up this type of schedule!
The Problem with Time in Excel
When it comes to entering Time Values, Excel will only accept a short list of Time Formats. For example, 6:00 AM is a valid Time Entry in Excel. However, Excel will treat 6:00 A.M. as a “text entry!” 6:00 AM is stored as the decimal value 0.25 in Excel. Decimals are numbers that Excel can calculate and increment. In this video, I demonstrate a shortcut that ensures that you always enter a Time Value as a Number.
How to Increment in Two-Hour Intervals
As I demonstrate in this video, use this formula to increment a series of two-hour time intervals:
=starting_cell + 2*(1/24)
In this case the (1/24) portion of the formula indicates a “one-hour segment.” 1 hour out of a 24 hour day. The +2 part of the formula indicates that you want to increment by 2 – of what? The answer is +2*(1/24) – by 2 hours.
To indicate a 15 minute increment use this formula:
=starting_cell + 15 * (1/24/60) – That is, divide each one hour segment (1/24) by 60 minutes (1/24/60) and multiply this by the number of minutes that you wish for your schedule.
Watch Part 1 of this Series – Increment Dates in Excel
View this Tutorial in High Defintion on YouTube
Click on this link to watch the video lesson in High Definition on my YouTube Channel – DannyRocksExcels
Get my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007 for only $29.97
Use Excel's AutoFill Tolls and Formulas to Increment Time Intervals [ 9:02 ] Play Now | Play in Popup | Download (2003)
