Date different by 1 day in cell and formula bar Excel 2013 seems to be having pr

Question

Date different by 1 day in cell and formula bar

Excel 2013 seems to be having problems displaying dates under specialized conditions in cells and the formula bar. If you enter a number date, e.g., 40000.999998, into a cell and format that cell as a date, the cell shows the date as 7/7/2009. In the formula bar, however, the date is displayed as 7/6/2009. I believe Excel is having difficulty with the day fraction. The cell is rounding the number up to the next whole number which translates to 7/7/2009 12:00AM, but the formula bar is rounding the number down and displaying 7/6/2009 12:00AM.

You may ask: Why would you enter 40000.999998 as the date? Here is another way this problem can show up. Enter 7/7/09 into a cell. Format the cell to show date and time. In the cell below, create a formula that equals the previous cell plus 1/24 (e.g., "=B10+1/24" in B11). This will give you 7/7/09 1:00AM. Duplicate this formula over the next 23 cells to cover the hours for all of 7/7/09. Each successive cell should add 1/24. This will give you the hours in 7/7/09. Copy the cell with 7/8/09 12:00AM and paste as a value. Format as a date and the problem reappears: the cell shows 7/8/09 12:00AM and the formula bar shows 7/7/09 12:00AM.

Thoughts on fixing this?

Explanation / Answer

The root cause of the problem (infinitesimally small fractions of a second) is the arithmetic =B10+1/24.

In your case, the remedy for both problems is:

=--TEXT(B10+1/24,"d/m/yyyy hh:mm")

or

=ROUND((B10+1/24)*1440,0)/1440

or

=ROUND((B10+1/24)*86400,0)/86400

The first form might be more convenient and apparent. But it depends on regional settings. For example, it would not work for me (US) because of the date format "d/m/yyyy".

Use either of the last two forms to avoid regional differences. Use the second form if you want accuracy to the minute. Use the third form if you want accuracy to the second.

Caveat: The result of the ROUND expression might not exactly match the internal representation of the equivalent time constant. That might affect the results of MATCH, VLOOKUP, etc.

Explanation....

Excel data and time is represented as a decimal number of the form d.t. The time component (t) is a decimal fraction of a day. As with most non-integers, it cannot be represented exactly in the binary form that Excel uses internally. Consequently, arithmetic often results in infinitesimal differences compared to mathematical expectations.

It is prudent to explicitly round non-integer arithmetic when we expect results to be accurate to a specific degree.

--TEXT(...) is one way to explicitly round time values. The double-negate converts the numeric text result to an actual number. Any equivalent arithmetic opertion has the same effect.

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.