by Allen Wyatt
(last updated May 23, 2015)
In Michael's work there is a subtle but often important difference between the time 24:00 and the time 00:00, but Excel doesn't support a time of 24:00. "Midnight" is 00:00:00 and is only recognized as belonging to the following day. Michael wonders if there is any way to specify a time of 24:00, i.e., a "midnight" ending a day, rather than starting a day, and operating on that date/time with normal functions?
Before answering the question, it is important to understand that midnight is a time unto itself; it is simply an instant, a fraction of a fraction of a second. When digitally denoting time with seconds, 00:00:00 is typically midnight, and 24:00:00 is a non-existant time. Why? Because the second before would be noted as 23:59:59 and when you add another second, the clock doesn't tick over to 24:00:00 and then the next second become 24:00:01. There are only 24 hours in a day, and those hours are numbered 0 through 23, not 1 through 24. So when you "tick over" from 23:59:59, the time becomes 00:00:00.
One could postulate that the day doesn't actually begin until 00:00:01 and that it ends at 24:00:00, but that is not the generally held feeling among most people who work with times. The feeling is that since the hours are counted using a zero-based range, consistency dictates that the seconds be counted using a zero-based range, as well. Just as each hour starts with zero seconds, the day should, as well.
Understanding that arguments could be made each way (as to how midnight should be designated within Excel), Microsoft chose to make everything—hours, minutes, and seconds—be in zero-based ranges. In determining how to store dates and times in a worksheet, Microsoft devised a numeric serial number system. The portion of the serial number to the left of a decimal point represents the number of days since a base starting date (usually January 1, 1900) and the portion to the right of the decimal point represents the percentage of a day since midnight. Thus, consider the following serial number:
This represents 3:17 pm on May 23, 1972. If the time clicked one more second (to 3:17:01 pm), then the portion to the right of the decimal point becomes .63681713, or 63.681713% past midnight. When the portion to the right of the decimal point is 0 (so the serial number becomes just 26442), then the time is 0% past midnight.
Interestingly, if you enter a time as 24:00:00, then that is what Excel displays—not because it allows such a time internally, but because it assumes you've entered an elapsed time. If you instead enter a date and time such as 5/19/2015 24:00:00, then Excel parses it as a date and time, converting it to 5/20/2015 12:00:00 AM.
So, the short response to Michael's original question is that his premise is technically incorrect—midnight is a unique transitional moment that doesn't really belong to either the day before or the day after. When accounting for seconds, however, a zero-based counting range is supported by Microsoft and consistency dictates that the time 00:00:00 belongs to the new minute, the new hour, and the new day. In such an accounting approach, there is no such time as 24:00:00, so it is automatically parsed as 00:00:00 on the new day.
For those who must keep a time on the previous day, the normal way of getting around the issue is to simply never enter 24:00:00. Instead, enter 23:59:59 and consider the second difference as "lost" for all intents and purposes.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9979) applies to Microsoft Excel 2007, 2010, and 2013.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
When you use a formula to come up with a result that you want displayed as a time, it can be tricky figuring out how to ...Discover More
Excel allows you to store times in a worksheet. If you want to use Excel to time certain events, there are a couple of ...Discover More
Dates and times are often standardized on UTC time, which is analogous to GMT times. How to convert such times to your ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.