Excel: Use Real Times

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


Problem: How does Excel deal with time?

Strategy: In Excel, 1 day is represented by the number 1. That means that time is stored as a decimal portion of a day. Go to any cell. Enter 0.5. Format the cell as time and you will get 12 noon, because half of the day has elapsed at noon.

I can name a few other times right off the top of my head. 6AM is stored as 0.25. 9PM is 0.875. 3AM is 0.125. Beyond that, it gets a tougher. What fraction of the day has elapsed at 3:42PM. That is a little tougher to calculate in your head.

  1. Format the numbers in row 1 as time and you get row 3.

    In the next figure, I used =RAND() in a bunch of cells and formatted those as time.

  2. Random decimals correspond to various times of the day.