SOLVED

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

Highlighted
New Contributor

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????

7 Replies
Highlighted

@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. 

Highlighted

@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.

Highlighted
Best Response confirmed by Bharath_Baiju (New Contributor)
Solution

@Bharath_Baiju 

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

image.png

On third step select Date and DMY

image.png

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

image.png

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

Highlighted

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

Highlighted
Highlighted
Highlighted

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.