Use Real Times

July 06, 2022 - by Bill Jelen

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.

Excel stores time as a fraction of a day. 0.01 is 12:14 AM. 0.125 is 3 AM. 0.25 is 6 AM. 0.5 is Noon, and so on. — Figure 558. 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.

While the previous example produced mostly "even" times, four cells with =RAND() produce random times. For example, 0.370956 is 9:36 AM. — Figure 559. Random decimals correspond to various times of the day.

This article is an excerpt from Power Excel With MrExcel

Title photo by Markus Spiske on Unsplash