Dealing with Midnight Ending a Day

Written by Allen Wyatt (last updated December 25, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


6

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-existent 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:

26442.636805556

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/2020 24:00:00, then Excel parses it as a date and time, converting it to 5/20/2020 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, 2013, 2016, 2019, and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Creating a Log/Log Chart

If you need to create a chart that uses logarithmic values on both axes, it can be confusing how to get what you want. ...

Discover More

Sending Drawing Objects to the Back or Front

Drawing objects can be placed in a document in such a way that they overlap with each other. If you want to arrange those ...

Discover More

Figuring Out the Low-Score Winner

Need to figure out the lowest score in a range of scores? Here's the formulas to get the information you need.

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Calculating Elapsed Time with Excluded Periods

When using Excel to calculate elapsed time, there can be all sorts of criteria that affect the formulas you would ...

Discover More

Converting Numeric Values to Times

If you have a bunch of times entered into cells without the colon between the hours and minutes, chances are good that ...

Discover More

Converting UTC Times to Local Times

Dates and times are often standardized on UTC time, which is analogous to GMT times. How to convert such times to your ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is five more than 3?

2022-01-09 12:23:09

Al

It is funny how many lengthy rants this small issue induced.
Don't even get me started on the inconsistent way we count years and centuries.


2022-01-08 17:41:07

Roy

@Martin Ramsch:

1. Sure, the best way forward, always, is to adapt to what a program offers and use it properly so that everyone has a consistent base to work from. Sadly...

2. That's not always your choice.

a. Your own proclivities might make you reject a thing or its effect. So what that someone can make an argument a different way is better? It's your choice, not his.
b. Your boss, or more widely, your organization decides differently. "QED" might not precisely fit in here, but you get the idea. Don't bother mentioning things like "Then it's time to update your resume!" because that really doesn't address the subject.
c. Standard practices and the closely related word "tradition" in your business, industry, whatever, are more important than the oft-bandied phrase "best practices" (which almost never are, people being people they are usually very, very congruent with their personal proclivities... odd that, huh?).
d. Related to the above, actual LEGAL concerns often arise, either direct violation of law, or liability producing concerns, some as esoteric as one "wording" (literal or metaphorical sense here) creating a legal contract with employees while a different one does not. In some countries, overtime or holiday concerns might occur if a worker's shift ends at 00:00 on the next day, the holiday or whatever, but not if it ends at 24:00, and that might be defined by what your record was recorded to be. I say "some countries" to cut short the argument many people would immediately engage in as they cannot pretend factual knowledge about all countries and therefore the knowledge to pooh-pooh "some countries" but this happens at all levels of government, foreign and domestic so...
e. Also related is traditional approaches having been determined by the technology previously in use for recording records. And of course, currently very widespread. All timeclocks came with a shift from 24:00 to 01:00 (as an example for this — my long experience would say reality was the opposite(!), but for this point...). Timeclocks were in very widespread use. So people commonly say 24:00, not 0:00. People being people, they sometimes (often), expect changes in something to lead to screwjobs that seldom resolve satisfactorily. And they're not wrong all the time, just 99% of it. But considering your constituency might demand you go with that flow, and slowly over time either migrate them little bit by little bit (and never screw it up) to the now more appropriate other way/s or take advantage of such efforts by others to do the same. Eventually, you get there, but NOT RIGHT NOW.

3. Sometimes software people choose stupid things. JUST. FREAKING. STUPID. CHOICES. ARE. MADE. Giving in to them is not necessarily any kind of best practice... Like MS with the start of time at 1-1-1900 (or 1904 in the alternative). And the wrong Leap Year there, supposedly on purpose, not from quick programming by a thoughtless or ignorant programmer/programming team. If I COULD overcome that by choosing something else I would but even writing something else leads to a worse problem if I send a file to anyone. (Sigh...) This matters. Though since Excel has fewer of these pr0gramming choices than it could, it isn't usually much of a problem with Excel. Also, Excel's expected (and realized) use base was 100,000,000 business users needing no dates before 1900 for every 1 genealogy professional cursing their lack so I give them a pass on this actual stupidity.

4. And perhaps most important: Excel's most interesting conceptual purpose is the extension of computing power and rigor to the masses who may create files of varying technical complexity and varying design effort. Particularly, they can create a simple, ad hoc file for some small purpose with minimal effort and minimal cost. No going to coders who then mix absolute requirements to modify your stated goals due to their languages not being good at what you want with their many-varied personal bugaboos that they call "best practices." Sometimes, they might even be right, but they don't know because honestly, it's jumbled together. Excel lets an amateur gin something up quicklike, use it for half a year, then forget it ever existed. All at the a teensy marginal cost. No programmers. And that very concept subsumes the lesser included crime of simply not caring, much less knowing, about "best practices." They just "Git-r-done!"

Preach on though brother. You might be blooming flower 893 on my personal list, but we're letting at least a thousand bloom all the time so be like a priest or minister: proselytize and monetize! You could win plenty of converts along the way with THE WAY. Yay.

But the fellow mainly wanted to display something guys! DISPLAY something as something. Not make Excel even less of a database or some similar "sin." Why shouldn't we help him do so if we can? 1,000 flowers blooming, not 1, and that one somehow just... yours. And Bill(ionaire) Gates' too. His, always his. Or Jeff's, or Warren's, or Elon's, or the Zuck's, or Sergey's...


2022-01-07 14:37:29

Martin Ramsch

@Roy: for denoting a *point in time*, I _always_ would stick to the standard display of 00:00:00 as this is unambigous.

The only scenario in which I would call midnight 24:00:00 would be if it was the *end of a period*. Because then this may be easier for the average person to grasp.

Let P_to be the end of a period. This might be displayed as:
=IF( INT(P_to)=P_to, TEXT(P_to-1, "MMM DD YYYY ""24:00"""), TEXT(P_to, "MMM DD YYYY hh:mm") )

Big disadvantage: you can't use this result directly in further calculations, but would have to use VALUE() to convert it back into a proper Excel datetime value.

Nonetheless Jan 07 24:00 is the identical point in time as is Jan 08 00:00. I'd tend much more to get users to understand this simple fact and get used to 00:00, instead of fiddling around with the display and making things just more error prone.


2021-12-28 03:59:59

Hans

I understand the 23:59:59 but that “lost” second I always add after the midnight calculation as + 0:0:1
“IT” Always crosses your path ...


2021-12-28 01:19:03

Darby Allen

From 1963 to 1987 I was a Telecommunications Controller in the Royal Air Force.
Our day began at 00:01, and ended at 23:59. Two free minutes every night!


2021-12-26 23:51:30

Roy

Well, given somebody for whom "Midnight = 24:00:00" you are likely also identifying somebody for whom "Midnight = 00:00:00" NEVER is a desired thing.

So the following is an approach one could use, simply for display. Time (and date) math would still work as usual and one would have to do whatever unique thing one desires that led to needing to see "24:00:00" but one WOULD see his desire. If that's something helpful, and bear in mind that physical appearance still matters in many cases even if the work under the hood is happening formulaically, then the following formatting string would do the job:

hh:mm:ss;"Negatory good buddy, can't display that.";24\:\0\0\:\0\0;@

(You know, adjusted to suit one's predilections and sense of fun. And available column width. Maybe easier to field as a problem call starting from here too, as opposed to starting from a display of "#####"...)


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.