AI how-to notes · 2026-09-18 · last run 2026-09-18 · 한국어 원문

Excel says two identical dates are not equal — hidden time and display format

One line summary

Two cells both printing 2026-09-18 return FALSE when you put =B2=B3 between them. One of them carries 2.30 pm, so what the cells hold is 46283.604166666664 and 46283. We put four variants in one cell each — including a date entered as text and a date that looks like a number — read back Value2 and the display format, and lining them up with INT, DATEVALUE and *1 turned every comparison TRUE. Running the same values through a sort and a filter, the filter counted 3 while COUNTIF counted 2, and they caught different rows.

Symptoms

• The dates look identical, and putting = between the two cells returns FALSE.
• Counting with a date condition returns fewer rows than there really are.
• You sort ascending and September 5 lands below September 20.
• The number of rows the filter shows for "September 18" differs from what a formula counts.

Test environment

We did not open an Excel window and click the menus in this round. We called the equivalent commands over COM and read the resulting numbers. The on-screen names may differ on your build.

Four cells that look like the same day

One column, four variants: a date with a time attached, a plain date, text with only the display format changed to a date, and a date left on the 「일반」 (General) format.

Below, B2, B3, B4 and B5 keep referring to these four cells. B2 and B3 do not differ by a single character on screen, and the comparison is FALSE. B4 has its display format set to yyyy-mm-dd and is still text underneath. B5 holds exactly what B3 holds and shows a five-digit number because its format is General.

Excel holds dates as serial numbers. The count starts from 1899-12-30 as 0, and in this test 2026-09-18 was 46283. Put =TEXT(B3,"yyyy-mm-dd hh:mm:ss") on the 46283 in B3 and you get back 2026-09-18 00:00:00. A day is 1, so 2.30 pm is 14.5 hours ÷ 24 = 0.604166... attached below the decimal point. The yyyy-mm-dd format only stops showing what is below the decimal point; it does not remove it.

Unfolded down to the time, it looks like this.

=TEXT(B2,"yyyy-mm-dd hh:mm:ss")  ->  2026-09-18 14:30:00
=TEXT(B3,"yyyy-mm-dd hh:mm:ss")  ->  2026-09-18 00:00:00
=B2-B3                           ->  0.6041666666642413

Subtracting the two cells puts the difference into a number. A result of 0 means the two values are identical. But a nonzero difference — even one smaller than 1 — does not mean the date is the same. 2026-09-18 23:00 and 2026-09-19 01:00 differ by only 2 hours (0.0833...), yet the dates themselves are different. To check whether the dates match, strip off the fractional part and compare the integers, e.g. =INT(A2)=INT(B2). The 0.6041666666642413 between B2 and B3 is the time difference, and since both cells fall on September 18, INT makes them equal.

Changing the display format does not make text stop being text

We took B4, the text one, and shuffled its format around.

바꾸기 전  Value2='2026-09-18'  표시 형식 yyyy-mm-dd  ISTEXT=TRUE
일반으로   Value2='2026-09-18'  화면 2026-09-18        ISTEXT=TRUE
날짜로     Value2='2026-09-18'  화면 2026-09-18        ISTEXT=TRUE
다시 입력  Value2=46283         화면 2026-09-18        ISTEXT=FALSE

(The four rows are "before the change", "set to General", "set to Date" and "re-entered"; 표시 형식 is the display format and 화면 is what shows on screen.)

The display format decides how something looks on screen, so it does not touch the text inside the cell. The last row, "re-entered", means we wrote the text that was in that cell back into the same cell unchanged. That is the moment it became 46283. Excel reads a value as a date at the instant the value is entered. Converting a whole column at once was not run in this round; the record of converting dotted dates in one pass with Text to Columns on the Data tab is in Cleaning up an inherited Excel table.

To count how many such cells are in a column, use =SUMPRODUCT(--ISTEXT(범위)). It does not tell you whether they are dates, only how many cells are text, so in a column that should hold nothing but dates, anything other than 0 is that many text entries.

Four ways to line them up

The 정렬!B2:B8 on the last row is the seven receipt dates in the next section (정렬 is the sort sheet).

INT discards everything below the decimal point. In a date serial number, everything below the decimal point is the time, so INT drops it to midnight. TRUNC gave the same result.

=INT(46283.604166666664)   -> 46283
=TRUNC(46283.604166666664) -> 46283
=INT(-1.5)                 -> -2
=TRUNC(-1.5)               -> -1

They diverge on negatives. INT rounds toward the smaller value and TRUNC cuts toward zero. Every date serial number we entered here was positive, and in that range the two functions returned the same value.

DATEVALUE turns text into a serial number. It only accepts certain notations, so we fed it seven.

The dotted 2026.09.18 was an error. Hand it text with a time attached and DATEVALUE takes only the date, so from a text state this one function also strips the time. Watch the last row, 20260918. DATEVALUE errors out, but *1 happily returns a number over twenty million. That is an easy place to assume it turned into a date and move on.

Comparing the displayed strings works too.

=TEXT(B2,"yyyy-mm-dd")=TEXT(B3,"yyyy-mm-dd")  ->  TRUE
=TEXT(B4,"yyyy-mm-dd")=TEXT(B3,"yyyy-mm-dd")  ->  TRUE

This makes both sides into text of the same shape and compares those. Comparing B2, with its attached time, and B4, held as text, this way returned TRUE for both. But what TEXT returns is text. If you leave that column in place and carry on doing date arithmetic on it, you run back into the problem in the DATEVALUE table above, so for a column that stays in the table and gets reused, INT is the better choice. For a one-off check, TEXT is easier.

What happens when you sort

We entered receipt dates for seven customers. Four cells are real dates, three are text. The three text cells also have the yyyy-mm-dd display format, so they are indistinguishable on screen.

The three text cells were pushed to the end regardless of their values. In an ascending sort, numbers come first and text follows, which drops September 5 below September 20. Here the display format is set to a date as well, so looking at the screen gives you no clue why the order came out like this.

Rows 2 and 3 are worth noting too. Both show September 18, and 카타상회 at 46283 (midnight) came first while 가나상사 at 46283.604166666664 (2.30 pm) came second. This is where the order can look scrambled within a single day.

The filter and the formula give different answers

We applied the condition "receipt date is 2026-09-18" to the same seven rows — once by passing Criteria1:="2026-09-18" to the auto filter, and once by formula.

Measuring row by row made the split obvious. COUNTIF counted 타파상사, the text one, and missed 가나상사, the one with a time; ">="&DATE counted 가나상사 and missed 타파상사. The two middle rows have the identical count of 2 and are counting different rows. Checking the numbers alone will not catch it.

If you read 3 off the filter and write "3 received on September 18" in a report while the person next to you pulls 2 with COUNTIF, neither of you is wrong — the column has mixed forms in it. Across these seven rows, =SUMPRODUCT(--(INT(B2:B8)=DATE(2026,9,18))) counted both the text row and the row with a time and gathered them into 3.

Results

The moment a column lands on your desk, print =SUMPRODUCT(--ISTEXT(범위)) and =SUMPRODUCT(--ISNUMBER(범위)) once each. If both are greater than 0, text and numbers are mixed in one column. This table gave 3 and 4.

What failed

• Setting the 「일반」 (General) display format with Range.NumberFormat = "General" was rejected. Switching to NumberFormatLocal = "G/표준" went through, and in the same spot NumberFormat = "@" (text) worked under its English name. We re-ran all four and recorded them in 04_실패재현_로그.txt.

---- ws.Range("A1").NumberFormat = "General"
결과: 실패
  (-2147352567, '예외가 발생했습니다.', (0, 'Microsoft Excel',
   'Range 클래스 중 NumberFormat 속성을 설정할 수 없습니다.', 'xlmain11.chm', 0, -2146827284), None)
---- ws.Range("A1").NumberFormatLocal = "G/표준"   결과: 성공 -> 'G/표준'
---- ws.Range("A2").NumberFormat = "@"            결과: 성공 -> '@'

(결과 is the outcome — 실패 failed, 성공 succeeded — and the quoted message is "cannot set the NumberFormat property of the Range class".)

• Putting a Python datetime straight into a cell shifted it by nine hours (0.375 of a day). We fixed it by computing the serial number ourselves and writing it through Value2.

넣은 값 2026-09-18 14:30   -> 셀 Text='2026-09-18 05:30:00'  Value2=46283.229166666664
    직접 계산한 값 = 46283.604166666664   차이 = -0.375
넣은 값 2026-09-18 00:00   -> 셀 Text='2026-09-17 15:00:00'  Value2=46282.625
    직접 계산한 값 = 46283.0              차이 = -0.375
Value2 로 넣었을 때        -> 셀 Text='2026-09-18 14:30:00'  Value2=46283.604166666664

(넣은 값 is the value written in, 직접 계산한 값 the serial number we computed ourselves, and 차이 the difference.)

• To count a whole column at once we tried =SUMPRODUCT(--(INT(N(B2:B8))=DATE(2026,9,18))) and got 0. Printing =N(B2:B8) on its own returned 46282 — just the first value of the range. Dropping N and applying INT directly gave 3.
• We wrote =?=순수날짜 as a table header and hit a COM error. Excel reads text starting with = as a formula. Changing the header to 「순수 날짜와 같은가」 ("is it equal to the plain date") went through (04_실패재현_로그.txt [3]).
• In the table used for the screenshots, we wrote the stored values in as text and Excel read them again. The cell we put '46283.604166666664' into shrank to 46283.6 on screen, and the cell with '2026-09-18' swallowed the leading apostrophe as a text prefix and became 2026-09-18'. Setting those cells' display format to @ first and rebuilding kept the text exactly as entered.

What we did not test

• We did not open an Excel window and click the menus. The filter was a condition passed to Range.AutoFilter, and we could not check where a value with a time attached lands in the date filter dropdown that folds into year, month and day.
• Whether the 1904 date system in the workbook options produces the same serial numbers — we did not switch it.
• Which century a two-digit year (26-09-18) is read as — not tried.
• Whether DATEVALUE accepts the same seven text forms on a PC with different regional settings, such as English Windows. The table above was measured on one PC, read as UI language 1042 and country setting 82.
• ISO notations such as 2026-09-18T14:30:00, or text with a time zone attached, were not tried.
• Which date group a date with a time attached falls into in a pivot table — not built.
• How long SUMPRODUCT takes over tens of thousands of rows — not measured. We only used seven.
• Calling Range.AutoFilter() with no arguments to clear the filter was blocked by 「Range 클래스 중 AutoFilter 메서드에 오류가 있습니다」 ("the AutoFilter method of the Range class failed"), so we turned it off with ws.AutoFilterMode = False. Whether that is the same action as clicking the filter button again on screen was not checked.

Related notes

• Cleaning up an inherited Excel table — merged cells, blank rows, text dates
• Combining several sheets and files into one table
• When an Excel formula will not calculate
• Excel IF — where the result flips at the boundary value, blanks and zero

Corrected 2026-09-28: The claim "a difference under 1 means the date is the same" was wrong. Replaced it with the INT() integer-part comparison and a midnight-boundary example (9/18 23:00 vs. 9/19 01:00).