I want to change the Date format from 29 Jul 2020 to 29/07/2020 but not able to do

[Bharath_Baiju]

Hello,

I want to change the date format from 29 Jul 2020 to 29/07/2020 automatically if i get this data .But I am not able to do . Or like is there method to do?

Is there any solution????

[#2]

@Bharath_Baiju Your dates are in fact texts. You need to transform these texts to dates. This can be done in various ways. One of them is to use 

=DATEVALUE(A1)

Enter it in an empty column and copy it down as far as needed. This creates a list of numbers (representing the number of days since 1 jan 1900). These you can then copy and paste as values back into column A, that you already formatted as "Date".

Another method would be to use Text to columns" in the data ribbon. In the 1st step choose "Delimited", leave the 2nd step as it is (default is "Tab"). In the 3rd step indicate it's a Data like "DMY", press Finish. 

[#3]

@Bharath_Baiju 

Select the cells with the dates.

Press Alt+F11 to activate the Visual Basic Editor.

Press Ctrl+G to activate the Immediate window.

Type or copy/paste the following line:

Selection.Value = Selection.Value

Press Enter.

Switch back to Excel.

[#4 · Accepted solution]

@Bharath_Baiju 

You may select the column and on ribbon Data-Text to Columns

On third step select Date and DMY

Result will be the date, you may apply any desired format to it

Above is for Windows, on Mac it shall be similar.

[#5]

@Sergei Baklan Isn't that what I suggested at the bottom of my answer above? :)

[#6]

@Riny_van_Eekelen , I'm very sorry, missed that. 

[#7]

@Sergei Baklan No problem!

[#8]

Thank you very much Riny and Sergei as of today I have learnt two methods for this as i am searching for the solution for long time.

@Riny_van_Eekelen, @Sergei Baklan