Today’s lesson builds on the knowledge we gained in the prior video. So, rather than calculate “Dates”in Excel, we will calculate “Time.” Excel stores both Dates and Times as Serial numbers in a cell. This enables us to calculate the amount of time between two dates or two times.

Learning how to properly format the cells that calculate time is crucial to achieving correct results. It is also important to observe the proper syntax for entering times in cells (00:00:00 PM) or Hours: Minutes: Seconds AM or PM)

Here are the steps to follow in this Excel Video Lesson:

1. Enter a Time function. e.g. =NOW() in a cell. Use the Keyboard Shortcut Ctrl+Shift+~ to reveal the serial number for that time.
2. Excel counts time beginning at 12:00 AM or Midnight. So, the serial number for 6:00AM is 0.25
3. If you only want to “Time Stamp” a cell, use the Keyboard Shortcut Ctrl+Shift+: (This entry will not update!)
4. The =TIME() function requires three arguments (Hours, minutes, seconds)
5. Be careful when performing calculations the show the difference between Start Time and End Time. If the End Time “spans midnight,” Excel will return an error unless…
6. You use an =IF() Function in this calculation =IF(End_Time<Start_Time, End_Time+1, End_Time)-Start_Time
7. To Increment a series of cells by 90 minutes use =Start_Time+TIME(1,30,0) and then copy the formula across the cells in the series.
8. Formats matter! To correctly calculate hours that exceed 24 hours: Use this format – [h]:mm in the cell that contains the calculation.

Find the Excel Video Lesson that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Related Video Lessons

• Learn how Excel calculates dates