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


10

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

Duplicating Styles without Dependency

Creating new styles in Word is a great way to ensure that your document has a uniform look. But what if you want to ...

Discover More

Getting Rid of Blue Squiggly Underlines

In an effort to make your writing better, Word uses "squiggly" underlines to mark things it thinks you may need to ...

Discover More

Removing All Bookmarks

Need to get rid of a lot of bookmarks all at once? Word doesn't provide a way to do it, but you can use the short macro ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Dealing with Small Time Values

It is no secret that you can store time values in an Excel worksheet. But do you really know how small of a time value ...

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

Automatically Converting to GMT

You know what time it is, right? (Quick; look at your watch!) What if you want to know what time it is in Greenwich, ...

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 one more than 7?

2024-06-15 16:49:24

Roy

In my last comment, I stupidly was crossing "sorting" with "filtering" so some/all of it really isn't "on point." Or accurate, eh?

When filtering (using Data|Filter), the values displayed in the feature's choices list are presented as displayed in the column.

Hence, if 45443.9999999653 has a display format (regular formatting, "Ctrl-1" type; I did not test Conditional Formatting) of "00000" in the cell so it shows "45444" then it will display in the filter feature's choices list as "45444" though I do not mean to imply it will be a separate listing from "real" 45444's so that two of them seem to be in the list, but rather it would be subsumed into them. As in, if you select the "45444" choice, a 45443.9999999653 displayed in its cell as 45444 will be swept up into Excel's choice of what to display.

If you display the cell value differently (even as "45443.9") then it will have its own entry and not be swept into the results.

45443.9999999653 displayed using Number formatting with 0 decimal places is presented as a distinct choice from 45444.0000000000, but not from a "clean" 45444 displayed with General formatting. And if you select the 45444, the 45444.0000000000 will not be in the results.

Interestingly, if you set that filtering for the column, say, and have values in, say, A1, when filtering column A, the calue in A1 will not be on the list unless it occurs in some other column A cell. If you select an exact range and it has blanks, say cells A2:A5, the value in A2 will not show in its list (if it is the only occurrence in the cells) but "blanks" appears and you select only that choice, the A2 value WILL be in the results. Other choices are precisely those choices so they exclude it, making the blanks option required in this case. No blanks in the cells and you will not see A2 in the results.

Not that the last bears on this topic. I just found it interesting.

So another approach would be to use a full 15 digit display so that the choices available are all listed. Though... if there are willy-nilly decimal portions to EVERY value, that choice list could be long. Though in order and actually manageable, though tedious beyond belief one supposes for a very long list.

The upshot is that if the values are not actually just what you want them to be, you will VERY UNSURPRISINGLY have troubles. Since number formatting cannot give you rounding choices (it rounds the display up or down using 4/5, period... even 45443.9999999653 needs to display 8 decimals to finally not display as 45444.000... even using the Number format because it isn't until then that it rounds the last digit up to a not-carry-the-one value (6 rounds up to 7 because of the 5 following it, but the 7 does not force the preceding 0's to round up causing a domino effect back to the integer portion).


2024-06-14 12:18:20

Martin Ramsch

Actually, the datetime filtering DOES round to full seconds, even if display format is "YYYY-MM-DD hh:mm:ss.000".
Then a datetime value of 45443.9999999653 correctly is displayed as "2024-05-31 23:59:59.997", i.e. correct day May 31st. BUT in column filter this date time is treated like June 1st!

If the decimal part of the seconds is not important, rounding down to whole seconds helps to make datetime display and filtering behave consistently.
I.e. =ROUNDDOWN(datetime*60*60*24,0)/60/60/24 , transforming 45443.9999999653 into 45443.9999884259 which exactly is 2024-05-31 23:59:59.000

If the decimal part of the seconds is important though, I don't know any way to make Excel filter such a column correctly by date.


And a late answer to Roy's lengthy post from 2022-01-08 17:41:07:
There is no more durable solution than the "small short-term workaround". :) So it actually pays to internally do all computations as consistently as possible from the start, and only for display in last step convert into the differing convention needed or wanted.

Regarding the 'midnight as end of day' vs. 'there is only 00:00': the latter is the way Excel works with dates. Understand it, use it.
And only at those _few_ places, where midnight is needed to be end of day (instead of start of next day) do a work-around.

As a variaton to my last workaround using TEXT, what also might work is splitting up datetime value into seperate date and time values, where midnight is stored (or computed) as date / time with time 1, e.g. 45456 / 1. Using display formats "YYYY-MM-DD" / "[hh]:mm" these will display as "2024-06-13" / "24:00", while 45457 / 0 will display as "2024-06-14" / "00:00". [Please read slash only as a separator, not as a division]
Sorting 1st level by date column and 2nd level by time column gives natural sort order, and filtering on date column works fine, too.
Getting the 'normal' datetime value is simply adding date+time, as usual.
Starting from a datetime value, the splitting-up can be done like
  date=IF( ROUNDDOWN(datetime,0)=datetime, datetime-1, ROUNDDOWN(datetime,0) )
  time=datetime-date

@Roy: no, I'm not expecting that this fits everybodies needs. It's just a suggestion, that might come handy and helpful.

Kind regards, Martin


2024-06-14 06:35:52

Roy

It doesn't round up, not that it matters in most practical situations.

It's just that 45443.xxxxx alphabetizes, so to speak, after 45443.0000000000. So it does that. And if you have the values displayed in a way that hides that it comes before values like 45444, you could think it does round.

The only solution is to remove the decimal portion (which in such an example is probably Time, and may or may not be desired anyway). Then things will filter as expected and subtraction of values will give expected results. If one must have both Date and Time information, splitting it into two columns, integer portions for Dates and decimal portions for Times, is a solid approach.

Plenty of variation in the approaches, in the exact way one gets the splitting and or discarding of the information to occur. Some rounding won't work the desired way, so you'd be back to where you started, even more firmly for that matter. So care is needed. Depends largely on the data and how/when you do the splitting/removal.

Sad though that Excel can't give an ability to provide a number format mask, say, in the filtering, and then apply that mask to the column values and filter based upon them. You could do that yourself, of course, with formulas but if you have to live with the column as it exists rather than edit it before import or use, and cannot use helper columns (which do not have to break the display... they can be on a separate sheet in the spreadsheet which you use for exactly such things), then the convenient "user-work-for-a-living" filtering cannot work and above we established you can't overcome it with the import or helper columns... then you're completely hosed. I mean, VBA could always be used but if the boss won't let you clean the data before import or use helper columns, he won't let you use VBA either, right?

But, if it ever matters, and often tiny detail, nitpicking details, DO matter a ton with Excel and programming in general, there is no rounding happening. It is just sorting using the whole value it is presented.

Ugh... and the funny thing where a couple-three arithmetic operations in the pathway to having a value in a column generates the tiny addition or subtraction of a wee value out at 15 digits? Like 4543.0000000002 instead of 45443 exact? THAT'S a killer to notice even if looking for "Time" data added on. But I think those would not happen here as they'd be way past the smallest unit of time Excel can work with, so far they could never even be rounded up to a thousandth of a millisecond and cause the filter sorting problem. But in other uses... Details are OFTEN the things you have to look at and interpret correctly in order to solve an Excel problem.


2024-06-13 09:30:54

Colin

Bigger problem, for me, is that our data comes with serial number values like 45443.9999999653. Excel treats this as the next day (45444) rather than just before midnight of day 45443. Not only does it misleadingly show the next day, it also FILTERS treating it as the next day. That is, for day-based filtering, Excel rounds up sometimes to the next day.


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.