Can't format dates in Excel

Question

Can't format dates in Excel

Gene Hudson 70

Data from MS Forms viewed in Excel; some dates are displayed at dd/mm/yy hh:mm. Want them in mm/dd/yy format, but no number of attempts to reformat have any effect.

User's image

0 comments

4 answers

Your answer

Answer 1

Ashish Mathur 101.9K Volunteer Moderator

Hi,

Try using a simple prompt in co-pilot to solve the problem. A prompt such as "Convert the entries in column A to proper date/time stamps. Get the result in column B. In the result column, ensure that the format is dd/mm/yyyy hh:mm"

This can also be solved using the following Python command.

=py and tab
=pd.to_datetime(xl("A2"))
Ctrl+Enter

I have not been able to test my Python solution due to some connectivity issues.

Hope this helps.

0 comments

Answer 2

Hi, I'm John! I will help you with this.

If the data was imported from Microsoft Forms, the dates might be stored as text, and changing the cell format will not affect text values.

Could you check a couple of things?

When you click one of the date cells, is the value aligned to the left or the right side of the cell?
If you select a date cell and change the format to General, does it remain as something like 10/06/26 16:39 or does it change to a number?
Also, which regional format is set in Windows? For example, is your region set to UK (dd/mm/yyyy) or US (mm/dd/yyyy)?

From the screenshot, the dates appear to be in dd/mm/yy hh:mm format, so Excel may be interpreting them according to your regional settings rather than as editable date values.

Let me know the answers to the questions above and I can suggest the next step.

Answer 3

Kai-L 14,395 Microsoft External Staff Moderator

Dear @Gene Hudson,

Good day, and thank you for reaching out to the Q&A Forum.

I understand that the date values from Microsoft Forms are showing in Excel as dd/mm/yy hh:mm, and that changing the date format to mm/dd/yy does not seem to have any effect. I’m sorry for the inconvenience this has caused. As a forum moderator, I am not able to access your account or review the backend systems directly to diagnose and resolve this for you. My role here is limited to providing general guidance and suggestions that users can try on their side.

To better understand the issue, could you please share a little more information?

How did you try to reformat the dates? For example, did you use Format Cells > Date, a custom format, or another method?
Are you viewing the live Microsoft Forms response workbook, or did you export/download the responses to Excel?
When you select one of the affected cells, what appears in the formula bar?
If you change one of the affected cells to General, does it turn into a number, or does it displayed as text/date?
Are all date values affected, or only some of them?

Please share what you have already tried and, if possible, provide one or two sample values exactly as they appear in the cell and in the formula bar. If you share another screenshot, please make sure to hide any personal or sensitive information. Thank you for your patience and understanding. I look forward to your reply.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Gene Hudson 70 Reputation points

2026-06-15T21:45:21.1566667+00:00

Kai-L,

Thanks for your help.

The problem occurs on about 10-20% of the entries.

I have tried reformatting both in the Forms response workbook, and in a separate spreadsheet I have downloaded the entries into.

Here is a screenshot of an affected entry. Note that the entry before and after are formatted mm/dd/yy.

If I take both entries (126 & 123) and format as number, I get...

The offending cell does not convert to a number.

Also, if I look at each cell in the formula bar, the unaffected cell displays with an AM or PM suffix after the time, where as the offending cell does not:

From an unaffected cell: 5/22/2026 1:07:00 PM

From an affected cell: 25/05/26 10:33

In some of the affected cells, they appear to be in text, while the unaffected cells are in date format (display a number if formatted as a number).

I can convert some of the affected entries into the desired format using the MID, DATEVAULE & TIMEVALUE FUNCTIONS as follows:

=DATEVALUE(CONCAT(MID(U172,4,2),"/",MID(U172,1,2),"/",MID(U172,7,2)," ",))+TIMEVALUE(RIGHT(U172,5))

Which converts a cell like the one on the right into the one on the far left:

But this does not work on all instances of affected cells...

Ideas?

I have been able to correct 'some' of the entries using the mid function to build a string with the month and day in the correct order and then applying the DATEVALUE and TIMEVALUE functions as follows:

=DATEVALUE(CONCAT(MID(U172,4,2),"/",MID(U172,1,2),"/",MID(U172,7,2)," ",))+TIMEVALUE(RIGHT(U172,5))

Using that function, a cell containing
Kai-L 14,395 Reputation points Microsoft External Staff Moderator

2026-06-15T23:07:33.8666667+00:00
Dear @Gene Hudson,

Thank you for the additional details and examples.

From my research, this appears to be a mixed date-value issue. Some entries are stored as real Excel date/time values, while the affected entries appear to be stored as text in a format. This explains why formatting works for some cells but has no effect on others. If Excel sees the value as text, changing the cell format alone will not convert it into a real date/time value.

Also, one important note is that when a date is stored as text, Excel cannot always know whether a value such as 05/06/26 means May 6, 2026 or June 5, 2026. If both the day and month are 12 or below, there is no reliable way to confirm the intended order from the text alone. Dates such as 25/05/26 are clearer because 25 cannot be a month, so that strongly indicates a DD/MM/YY format.

You can try one of the following approaches:

Option 1: Use Power Query

Select any cell in your data table.

Go to Data > From Table/Range (this loads your data into Power Query).

In Power Query Editor, select the problematic date column.

Right click a column header, and then select Change Type > Using Locale.

Data Type: Date/Time

Locale: English (United States) (for MM/DD/YY)

Click OK.

Go to Home > Close & Load

Power Query is excellent at handling these inconsistent imports.

For reference: Set a locale or region for data (Power Query) | Microsoft Support

Option 2: Use a formula

You can try this formula:

=IF(ISNUMBER(A1),A1,LET(x,TRIM(SUBSTITUTE(A1,CHAR(160)," ")),d,TEXTBEFORE(x," "),t,TEXTAFTER(x," "),p,TEXTSPLIT(d,"/"),DATE(2000+--INDEX(p,3),--INDEX(p,2),--INDEX(p,1))+TIMEVALUE(t)))

This formula keeps real Excel date/time values unchanged. If the value is text, it attempts to convert the text from DD/MM/YY HH:MM into a real Excel date/time value.

Please note that this formula assumes the affected text dates are in DD/MM/YY order. If some text dates were actually entered in MM/DD/YY order, then any ambiguous values where both the day and month are 12 or below may need to be checked manually against the original source.

Please try the Power Query approach or the formula above and let me know how it goes. If some entries still do not convert correctly, feel free to share one or two sample values exactly as they appear in the formula bar, and I’ll be happy to help adjust the formula further.

Have a wonderful day!
Angga Khalifah 0 Reputation points

2026-06-16T14:27:52.5466667+00:00

we got same condition with excel on office 365, and lookslike this condition Only from June 1–12, 2026.
Kai-L 14,395 Reputation points Microsoft External Staff Moderator

2026-06-16T19:48:30.3666667+00:00
Dear @Angga Khalifah,

Good day, and thank you for reaching out to the Q&A Forum.

I understand that you are seeing a similar issue in Excel for Microsoft 365. Since this may be related to how the date values were imported or interpreted, could you please first try the steps I shared above, especially checking whether the affected values are stored as real Excel date/time values or as text?

You may also try the Power Query approach or the formula method mentioned earlier to convert the affected text-based dates into real Excel date/time values before applying the desired date format.

If the suggested steps do not resolve the issue, I recommend creating a new thread with your specific details, such as:

Whether the data came from Microsoft Forms, an exported Excel file, or another source.

A few sample affected values exactly as shown in the formula bar.

Whether formatting the affected cells as Number converts them into serial numbers or leaves them as text.

Your Excel version and region/date settings, if possible.

Creating a separate thread will help keep your case organized and allow the community to review your specific scenario more closely.

Thank you for your patience and understanding. I hope the steps above help, and I wish you all the best!
Kai-L 14,395 Reputation points Microsoft External Staff Moderator

2026-06-16T20:35:16.4233333+00:00

Dear @Gene Hudson,

It has been a while and I am writing to see how things are going with this issue. If there’s anything else I can assist you with or if you need further clarification, please don’t hesitate to let me know, I’m here to help. Looking forward to hearing from you. Wishing you a great day ahead!
Ashish Mathur 101.9K Reputation points Volunteer Moderator

2026-06-16T22:52:22.2533333+00:00

Hi,

Please follow my suggestion from the previous post

Answer 4

The dates from Microsoft Forms are likely stored as text, not real Excel date values. Changing the cell format alone cannot change the display if the underlying value is text.

Convert the text to real dates, then apply the desired format:

Insert a new helper column next to the Forms date column.
Assume the original value (for example 10/06/26 16:39) is in A2.
In the helper column row, enter a formula that converts the text to a date/time value, for example:
```
   =DATEVALUE(LEFT(A2,8)) + TIMEVALUE(MID(A2,10,5))
```
or use a parsing formula appropriate to the exact text pattern.
Fill the formula down for all rows.
Select the helper column, press Ctrl+1 to open Format Cells.
On the Number tab choose Date, or Custom and enter mm/dd/yy hh:mm (or mm/dd/yy if time is not needed) in Type.
Once the helper column shows the dates correctly, copy it and use Paste Special → Values over the original column if needed.

If Excel is auto-formatting new entries incorrectly, first set the column to Text (Ctrl+1 → Number tab → Text) before importing or pasting, then use formulas or Text to Columns to convert into the required mm/dd/yy structure.

References:

Share via

Can't format dates in Excel

4 answers

Your answer