How to Change Date/Time Format for whole column

Natasha1993 

A possible 365 formula would be

= MAP(dateImport,
    LAMBDA(import,
      LET(
        t?, ISTEXT(import),
        v,  TEXTSPLIT(import, {"/"," "}),
        d,  IF(t?, INDEX(v, 2), MONTH(import)),
        m,  IF(t?, INDEX(v, 1), DAY(import)),
        y,  IF(t?, INDEX(v, 3), YEAR(import)),
        DATE(y, m, d)
      )
    )
  )

Natasha1993 

You started with the problem of dates as text in US format. 

The ideal solution is to import the text using PowerQuery which can be used to transform the data before it is loaded to the sheet.

Once such a file has been loaded to the workbook, however, you have two distinct problems.  The first is that every value that Excel has converted to a date will be wrong.  Excel will show the 4th March when the intention was 3rd April.  The second is that your non-US version of Excel will have given up when asked to name the 13th month and left the US formatted date as text.

The solution is to test the date field to see whether you have text or a number and to pick the day, month and year using the appropriate formulas.  The DATE function creates an appropriate datevalue in either case.

Natasha1993 

Format a date the way you want

You can easily update the date/time format for a whole column in Excel 365.

Here is how you can do it:

1. Select the entire column containing the date/time values. You can click on the column header to select the entire column.
2. Right-click on the selected column and choose "Format Cells" from the context menu. Alternatively, you can use the keyboard shortcut Ctrl + 1.
3. In the "Format Cells" dialog box, go to the "Number" tab.
4. In the Category list, select "Custom" or "Date" (depending on your preference).
5. In the "Type" field, enter the desired date/time format. For DD/MM/YYYY HH:MM, you can use the following format: dd/mm/yyyy hh:mm.
6. Click "OK" to apply the new format to the selected column.

After following these steps, the entire column will be updated to display the date/time values in the format you specified. Excel will recognize the format and display it accordingly, regardless of the default regional settings on your PC.

Please note that changing the format of the column does not modify the underlying values. It only changes how the values are displayed. If you need to convert the values to a different format or adjust the actual date/time values, you may need to use formulas or other techniques specific to your data. The text was created with the help of AI, the link is from Microsoft.