Dealing with Midnight Ending a Day

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).

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

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.

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.

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.

@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...

@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.

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 ...

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!

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 "#####"...)