Hi all I want to change the date format from dot to slash and I have tried all ways of changing the date from control panel and also from the "Format option" in excel but nothing works.

Please would anyone can recommend how can I do this bulk change of format of date except doing it manually. Any advices would be much appreciated.

CodePudding user response：

First of all, make sure the value you have in the spreadsheet is a valid date.

Excel might interpret the value as plain text.

After you make sure it is a date, then you can change the date format using the cell attribute editor

You can use formatting like dd/mm/yyyy or mm/dd/yyyy

CodePudding user response：

You can use substitute to replace the dot with a slash, then wrap that with datevalue to convert to a date. Lastly, format the cell as your desired Date format (such as Short Date or Custom).

Below, column A is your original data.

Column B contains the following formula:

=DATEVALUE(SUBSTITUTE(A2,".","/"))

Output: