This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Use Real Times
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.
Figure 565 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.
Figure 566 Random decimals correspond to various times of the day.