Forum Discussion

Nina_SC's avatar
Nina_SC
Copper Contributor
Jun 07, 2019
Solved

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 flas...
excel
  • Berndvbatanker's avatar
    Berndvbatanker
    Jun 07, 2019

    Nina_SC 

    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 Sub

     

    Best regards from germany

    Bernd

    http://www.vba-tanker.com

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 07, 2019

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

alcar1511's avatar
alcar1511
Copper Contributor
Nov 15, 2019

SergeiBaklan 

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.

 

 

 

 

 

Resources