How to change international date formatted as text to US date format

[Ttexqu]

I want to transform ‘13.08.2023 to 08/13/2023.  Regular formatting is not working.

[#2]

@Ttexqu 

Select a column with such dates.

On the Data tab of the ribbon, click 'Text to Columns'.

Select Delimited, then click 'Next >' twice.

Select DMY from the drop-down next to Date.

Click Finish.

Apply the desired date format.

[#3]

It only converted few of them. Not sure why? The entire column is formatted the same way.

[#4]

@Ttexqu 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#5]

suppose cell A2=13.08.2023 =WEBSERVICE("http://e.anyoupin.cn/eh3/?regreplace~(\d{2})\.(\d{2})\.(\d{4})~\2/\1/\3~" & A2)

[#6 · Accepted solution]

@Ttexqu 

If you want to convert the dates in the entire sheet from the international date format to the US, you can use the "Find and Replace" feature in Excel.

Here is how you can do it:

1. Press "Ctrl + H" to open the "Find and Replace" dialog.
2. In the "Find what" box, enter a period (.) to find all the cells with the international date format.
3. In the "Replace with" box, enter a forward slash (/) to replace the period with a slash.
4. Click "Replace All" to replace all the periods in the sheet with slashes. This will convert the dates from the international format to a format that Excel recognizes as dates.
5. Select the entire column that contains the converted dates.
6. Right-click on the selected cells and choose "Format Cells."
7. In the "Format Cells" dialog, go to the "Number" tab, and select "Date" from the list on the left.
8. From the "Locale (location)" drop-down, choose "English (United States)" or any other locale that uses the US date format.
9. Select "Date" from the list of formatting options, and choose "MM/DD/YYYY" from the drop-down.
10.  Click "OK" to apply the US date format to the selected cells.

By following these steps, you should be able to convert all the dates in the entire sheet from the international format to the US date format without using VBA.

If the "Text to Columns" method is not converting all the dates correctly, it is possible that some of the dates might be in a different format or contain leading/trailing spaces that are causing the issue.

In such cases, you can try using the "DATEVALUE" function to convert the dates. Here is how you can use the "DATEVALUE" function to convert the international date format to the US date format:

1. Assuming your date '13.08.2023 is in cell A1, in an empty cell (let's say B1), enter the following formula:

=DATEVALUE(MID(A1, 4, 2) & "/" & LEFT(A1, 2) & "/" & RIGHT(A1, 4))

    2. Press Enter. This formula will convert the text-formatted date in cell A1 to a proper date value in cell B1.

   3. If the result in cell B1 is a correct date value (e.g., 08/13/2023), you can now copy the formula down the entire column to convert all the dates.

   4. Once all the dates are converted to the US date format, you can select the column with the converted dates, copy it, and then use "Paste Special" to paste it as values over the original column. This step will convert the formulas to actual date values.

Now, the entire column should be formatted as proper dates in the US date format. If the formula still does not work for some cells, it is possible that there might be inconsistencies or issues with the data in those cells. You may need to manually check and correct those cells if necessary or you can use a VBA macro to achieve this.

Here is a step-by-step guide:

1. Press "Alt + F11" to open the Visual Basic for Applications (VBA) editor.
2. In the VBA editor, click "Insert" from the menu and then choose "Module" to insert a new module.
3. In the module window, paste the following VBA code:

vba code:

Sub ConvertToUSDateFormat()
    Dim cell As Range
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        For Each cell In ws.UsedRange
            If IsDate(cell.Value) Then
                cell.Value = Format(cell.Value, "mm/dd/yyyy")
            End If
        Next cell
    Next ws
End Sub

   4. Close the VBA editor.

   5. To run the macro, press "Alt + F8" to open the "Macro" dialog. Select the "ConvertToUSDateFormat" macro from the list and click "Run."

This VBA macro will loop through all the worksheets in the workbook and convert any cell with a valid date value to the US date format (mm/dd/yyyy). It will permanently change the format of the dates in the entire sheet.

Please note that using VBA macros can have permanent effects on your data, so it's always a good idea to create a backup of your workbook before running any macros. After running the macro, all the dates in the entire sheet should be converted to the US date format. The text and all steps have been created with the help of various AI's.

My answers are voluntary and without guarantee!

Hope this will help you.

[#7]

@NikolinoDE 

Thank you so much.  The Datevalue formula worked.