Failed to transform date format from m/dd/yyyy to dd/mm/yyyy

Results Are As Worker

Hi,

Try this approach.

1. Select the dates in column E and go to Data > Sort. Sort the table on column E in ascending order
2. With that range selected, go to Data > Filter > Number Filter/Text filter > Equals. Type * and enter
3. With that range selected, go to Data > Text to Columns > Delimited > Next > Next > Date > MDY > Finish

Hope this helps.

Hi Kritiman Singh,

• By default excel use our machine date time format. as per your screenshot your machine date time format represents 'mm/dd/yyyy'
• enter your desired date like this format 'yyyy/mm/dd'. then right click goto Format cells.
• select custom enter your desired format 'dd/mm/yyyy' the click ok button. automatically changed to your desired date time format.

Thanks

Hi @Kritiman Singh, 

Thank you for posting your question in the Microsoft Q&A forum. 

Regarding your request to change the date format from mm/dd/yyyy to dd/mm/yyyy, as the AI recommended above, you could follow the step by step instructions below for more details: 

Example: If you have a list of dates currently displayed in mm/dd/yyyy format: 

1. Select the range of cells containing the dates you want to change. 
2. Right click on the selected cells and choose Format Cells. 
3. In the Number tab, select Custom. 
4. Enter the format dd/mm/yyyy. 
5. Click OK. 

The date format will now be changed from mm/dd/yyyy to dd/mm/yyyy. 

Thank you again for your time and understanding. If this does not fully resolve the issue, please feel free to share more details about your situation so I can assist you more effectively. 

I appreciate your patience and look forward to your response.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".                 

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

In the screenshot the dates in the right-hand column are text in m/dd/yyyy format. To convert them to real dates and display as dd/mm/yyyy, first convert the text to dates, then apply a custom date format.

1. Insert a new helper column next to the text dates.
2. Suppose the first text date is in M2. In the helper column (for example N2) enter a formula that converts the text to a date value, such as:

      =DATE(RIGHT(M2,4), LEFT(M2,FIND("/",M2)-1), MID(M2,FIND("/",M2)+1, FIND("/",M2,4)-FIND("/",M2)-1))
   

   Fill this formula down for all rows.
3. Select the helper column with the formula results.
4. Press Ctrl+1 to open Format Cells.
5. On the Number tab choose Custom.
6. In Type, enter:

      dd/mm/yyyy
   

   and select OK.
7. If needed, copy the helper column and use Paste Special > Values over the original text dates, then delete the helper column.

This ensures the values are stored as true dates and consistently displayed as dd/mm/yyyy.

References:

• Format a date the way you want in Excel
• Format a date and time field
• Converting date format - Microsoft Q&A