Dealing with Midnight Ending a Day

by Allen Wyatt
(last updated April 23, 2020)


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


Correctly Saving Delimited Files

Delimited files are often created through Excel so that your data can be exported to other programs. If the delimited ...

Discover More

AutoFilling with Weekdays

Need to fill a range of cells with the days of the week? Excel makes it easy to do so using AutoFill.

Discover More

Only Showing Readability Statistics

Perform a grammar check, and Word displays some statistics that represent an analysis of your words. By writing a macro ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Adjusting Times for Time Zones

Collect a series of times in a worksheet, and you might need to adjust those times for various time zones. This involves ...

Discover More

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

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

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

View most recent newsletter.


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 six more than 6?

2015-05-26 05:12:19



Thanks for taking the time to read my post, both of your suggested solutions work really well.

MOD is a new concept for me and will no doubt prove useful in the future as well.

Cheers, Phil

2015-05-26 04:59:57

Frank Tonsen


use a user-defined time-format (hh:mm)

start end Duration
23:50 00:05 00:15
22:00 06:00 08:00


2015-05-25 09:55:01


Apologies - just read the related article 'working with elapsed time' that looks like it will sort me out here.

2015-05-25 09:51:29


This is a genuine issue for me and one that I don't have a proper answer to. Essentially my clients' operating day typically runs till 3am. Any revenue or operations between 00:00 and 03:00 are judged to be on the previous day. When you want to know how late something may have operated, you sometimes have to evaluate a pre midnight scheduled time i.e. 23:50 with a post midnight operated time say 00:05. When you take one from the other you get #### in time mode. Does anyone have a better way of working round this issue?

2015-05-24 13:20:10

Michael (Micky) Avidan

Can you, please, explain (in details) what kind of calculation are you talking about ?
It will be nice to see/get a Workbook with your EXACT needs (while representing 4-5 results (calculated by you & typed as the required results).
You may upload the WB to whatever File-hosting site you feel suitable and presenting us a link for downloading.
Michael (Mickey) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)

2015-05-24 12:43:38


What Adam P said. I have gotten around the problem, which I believe is one of philosophy (or perhaps politics, when political bodies stop their clocks in order to finish business by a mandated date), by placing the date and time of day in adjacent cells for display purposes, and using their sum for computation.

2015-05-24 00:22:12

Adam Pemberton

A very thorough readable response Allen. Nice work.

2015-05-23 09:20:05

Adam Nimmo

Maybe using the custom format for the cell will help in some cases.
Try the format [hh]:mm and the hours will not reset to zero at the end of the day. However they do not reset to zero after midnight either they just keep increasing so it could lead to other problems for you. Unless you can switch the format on and off in a macro as required.

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

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.