Forum Discussion
Convert Text to Date Format
Hello everyone,
I'm just an excel newbie and I've been finding ways on how to convert a date text string to date format. I find it peculiar that after transferring all my CSV files through a flash drive, then into another machine, my dates messed up but it was all in the correct format before. See attached photo below for the outcome. How do I fix this?
Hi Nina,
you can use this macro, see attached file to convert dates. It converts all dates in selection to real date.
Sub KonvertDate()
Dim rngCell As Range
For Each rngCell In Selection
rngCell.Value = CDate(rngCell.Value)
Next rngCell
End SubBest regards from germany
Bernd
http://www.vba-tanker.com
11 Replies
One extra note to flag, don't open then save your CSVs, use Power Query to pull the data in without opening the original CSVs.
In your example most solutions will leave the circled values as 3rd January, 3rd February but I'm guessing the original dates were 1st and 2nd of March
Wyn Hopkins Hi wyn, thank you so much for your suggestion. I tried applying it and used power query in power bi -- and after changing the locale still nothing has changed. The error was still there. I don't know where the error is coming from but would you mind taking a look at my file? It would mean a lot and thank you so much.
If you don't have the original unsaved then you may need to use a formula approach like the attached
Nina_SC ,
You may select your column, on ribbon Data->Text to Columns. On second step of the wizard select as delimiter any symbol which is not appears in you data
On third step select Date and MDY
It is assumed your default date format is m/dd/yyyy
I have a similar problem with internationally formatted data not being recognised as dates and times or sort correctly.
The first set of date times are in PDT American format - 10/5/19 2:09:56 PM PDT
The second are in ISO 8601 format - 2019-10-02T03:54:00.000Z
I'm trying to find the feature that turns on Excel support for recognise these standard data formats so I can perform sort them and use them as an axis in graphs I want to generate.
I'm assuming that as the market leading provider of office based data manipulation and presentation tools Microsoft Excel would have implemented all the common international standards used for date and time date.
Hi Sergei,
Thank you for your reply. I did what you have instructed but still no visible change though. Is there another workaround I could do?Nina
Your issue is common when going from data in one date format e.g. US to another e.g. UK / Australian
I did a video on it here on how Power Query in Excel can fix it
Hi Nina,
you can use this macro, see attached file to convert dates. It converts all dates in selection to real date.
Sub KonvertDate()
Dim rngCell As Range
For Each rngCell In Selection
rngCell.Value = CDate(rngCell.Value)
Next rngCell
End SubBest regards from germany
Bernd
http://www.vba-tanker.com
