Sep 22 2020 01:07 AM
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????
Sep 22 2020 01:27 AM
@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.
Sep 22 2020 01:32 AM
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.
Sep 22 2020 02:36 AM
SolutionYou 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.
Sep 22 2020 02:40 AM
@Sergei Baklan Isn't that what I suggested at the bottom of my answer above? :)
Sep 22 2020 02:41 AM
@Riny_van_Eekelen , I'm very sorry, missed that.
Sep 22 2020 02:42 AM
@Sergei Baklan No problem!
Sep 22 2020 04:17 AM
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.
Sep 22 2020 02:36 AM
SolutionYou 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.