Date-Time parsing problem

Question

Date-Time parsing problem

Atakul Umit 0

The following ISO formatted date-time fields fail on 3rd and 7th lines. As far as I understand from my trials if millisecond's part starts with 0, then the parser fails to detect text as a valid date-time field.

(Microsoft® Excel® for Microsoft 365 MSO (Version 2606 Build 16.0.20131.20152) 64-bit)

2026-07-10 13:40:08.360

2026-07-10 13:40:18.163

2023-10-17 10:18:31.080

2024-05-29 13:00:09.307

2024-05-29 18:44:48.410

2026-06-17 11:46:36.900

2026-06-17 11:46:40.010

2026-05-22 11:07:46.827

2026-04-28 11:11:47.877

2026-04-28 09:28:29.920

Ashish Mathur 102.2K Reputation points Volunteer Moderator

2026-07-24T23:10:06.3533333+00:00

HI

What exactly are you trying to do? What result are you looking for?
Atakul Umit 0 Reputation points

2026-07-25T19:36:47.15+00:00

Hi,

I am not watching for a result just reporting this as a bug.

There are inputs all in the same syntax and excel automatically converts them to Date-Time values, but not all of them.

This is not a format selection problem clearly.

The parser should be fair on the inputs and parse them or not if syntax is same and there is no semantic error such as a day value greater than 31.

I can write a custom formula to cut the milliseconds part or change the value in another way, there can be countless solutions, for sure.

4 answers

Your answer

Ashish Mathur 102.2K Reputation points Volunteer Moderator

2026-07-24T23:10:06.3533333+00:00

HI

What exactly are you trying to do? What result are you looking for?
Atakul Umit 0 Reputation points

2026-07-25T19:36:47.15+00:00

Hi,

I am not watching for a result just reporting this as a bug.

There are inputs all in the same syntax and excel automatically converts them to Date-Time values, but not all of them.

This is not a format selection problem clearly.

The parser should be fair on the inputs and parse them or not if syntax is same and there is no semantic error such as a day value greater than 31.

I can write a custom formula to cut the milliseconds part or change the value in another way, there can be countless solutions, for sure.

Answer 1

Alex James 0

This looks more like Excel treating those values as text than an issue with the milliseconds themselves. Leading zeros in the fractional seconds (like .080 or .010) should be valid ISO timestamps.

A quick workaround is to import the data as text first, then convert it with a formula or Power Query instead of relying on automatic detection. It may also help to check your regional date/time settings, as they can affect parsing. The official Excel documentation has more information on supported date formats.

0 comments

Answer 2

Barry Schwarz 5,941

The problem is not that the value is not recognized by the parser. The problem is that the parser does not default to the correct format to display the value. I have no idea why a leading 0 in the millisecond value causes this but the solution is simple - use a custom format. I tested your values with both of the following custom formats and the both values displayed properly.

m/d/yyyy h:mm:ss.000
yyyy-m-d h:mm:ss.000

Atakul Umit 0 Reputation points

2026-07-24T15:25:25.7633333+00:00

Any custom date format did not work. Please find default formatted (left) and custom formatted cells (right) below. As you can see there is no difference.
Barry Schwarz 5,941 Reputation points

2026-07-24T22:52:56.3466667+00:00

What are the actual formats used for each of these columns?

Here are my results from pasting lines 2-4 of your input and setting the format.
Atakul Umit 0 Reputation points

2026-07-25T00:20:00.59+00:00

Hi Schwarz, your custom format produces same data with provided data, so it cannot be resolved if custom formatting applied or not. Because input and formatted output is same.

But I am just adding my formatting selection: please read g.aa.yyyy ss:dd as d.mm.yyyy hh:mm
Barry Schwarz 5,941 Reputation points

2026-07-25T04:36:00.8566667+00:00

So change the format to m/dd/yyyy h:mm:ss.000 or to h:mm:ss.000 dd/mm/yyyy or whatever else you need to convince yourself the data has been entered properly and the problem you describe in the original question is due solely to the system using an undesirable default format.

Answer 3

Atakul Umit 0

Hi,

It doesn't matter if input in provided as a simple copy paste operation on just entering the string from formula bar.

The interesting thing is that if fractions are not allowed or not recognized by the parser itself than why some of them is working but others are not. Since they all have the same format and align with the ISO standards.

Please just copy and paste the values that I have provided previously, you'll see that lines 3 and 7 will not be recognized as date-time values and be shown as it is. Just change first millisecond chars from 0 to another digit on formula bar or inside the cell, then you'll see that parser will work, and the field will be recognized as Date-Time value.

Thanks

Andreas Killer 144.1K Reputation points Volunteer Moderator

2026-07-25T08:27:55.8766667+00:00

This is a bug, as workaround add a 1 to the end of each line and paste that into Excel. Here is a screenshot from my German Excel to show that it works (Date format is dd.mm.yyyy on my system).

If you regularly need to import large amounts of data into Excel, save the data as a CSV and import it using Power Query. You won't encounter this problem there.

Andreas.

Answer 4

Ana Le 1,890 Independent Advisor

Hi,
Thank you for providing the sample values.

I reviewed the information you shared. Based on the data alone, there isn't enough information to determine why only some of the values are not recognized.

Microsoft documents that Excel does not support all ISO 8601 date/time formats in every import scenario, particularly formats that include fractional seconds. You can find the documentation here: XML Schema Definition (XSD) data type support | Microsoft Support

There is also a Microsoft Q&A discussion covering ISO date/time recognition in Excel: Excel does not recognize ISO formated date time (yyyy-mm-ddTHH:MM:ssZ) - Microsoft Q&A

To help identify the cause, could you please provide the following information?

How are these values being brought into Excel (copy/paste, opening a CSV file, Power Query, or another import method)?
What is the expected result, and what happens on the rows that fail?

With this information, I can further investigate whether the behavior is related to the import process or whether additional investigation is needed.

Atakul Umit 0 Reputation points

2026-07-25T17:32:49.7833333+00:00

Hi,

First, there is nothing with XSD in this flow.

I have checked the second link you provided thanks. It says that ISO formatted text cannot be parsed (in short). The text I have provided is not a standard ISO 8601 formatted text because it lacks the letter T in the middle.

The samples provided before has been derived from SQL server.

They are in SQL Server formatting convention, not the canonical ISO 8601 representation.

My question is that if Excel cannot parse the texts like YYYY-MM-DD HH:MM:SS.mmm, then why some of them have been parsed but not the others. This should not be the standard way?
Barry Schwarz 5,941 Reputation points

2026-07-26T04:04:41.3033333+00:00

Change the format of the third data cell (the one that should end in .080) to number with 11 decimal places. Verify the value displays as 45216.42952638890. Widen the cell if necessary. This demonstrates that Excel recognized the input as a date-time value.

Now apply the custom format h:mm:ss.000 dd/mm/yyyy to this cell. Verify the value displays as 10:18:31.080 17/10/2023. Widen the cell if necessary. This demonstrates that the problem reported in your original question is a format issue and not a parsing or processing one.

Apply a custom format that displays what you want to see and apply it to the entire range of data cells..
Ana Le 1,890 Reputation points Independent Advisor

2026-07-27T07:26:15.51+00:00
Thank you for the additional information and for sharing the results from your testing.

I reviewed the discussion carefully. Based on the results shared in this thread, the behavior is inconsistent:

Some values in the YYYY-MM-DD HH:MM:SS.mmm format are recognized as valid Excel date/time values.

Other values in the same format remain as text, even though they follow the same pattern.

At this time, I haven't found any official Microsoft documentation describing this behavior as expected or explaining why values with the same format are handled differently.

Since the behavior can be reproduced with the sample values you've provided, I recommend submitting it through the Excel Feedback Portal so it can be reviewed by the Excel product team:

Excel · Community

If Microsoft publishes any official guidance or confirms the behavior, that information will be available through the Feedback Portal and can serve as a reference for others experiencing the same issue.