SOLVED
Home

Convert Text to Date Format

%3CLINGO-SUB%20id%3D%22lingo-sub-676778%22%20slang%3D%22en-US%22%3EConvert%20Text%20to%20Date%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-676778%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20just%20an%20excel%20newbie%20and%20I've%20been%20finding%20ways%20on%20how%20to%20convert%20a%20date%20text%20string%20to%20date%20format.%20I%20find%20it%20peculiar%20that%20after%20transferring%20all%20my%20CSV%20files%20through%20a%20flash%20drive%2C%20then%20into%20another%20machine%2C%20my%20dates%20messed%20up%20but%20it%20was%20all%20in%20the%20correct%20format%20before.%20See%20attached%20photo%20below%20for%20the%20outcome.%20How%20do%20I%20fix%20this%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-676778%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-676834%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Text%20to%20Date%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-676834%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356309%22%20target%3D%22_blank%22%3E%40Nina_SC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Nina%2C%3C%2FP%3E%3CP%3Eyou%20can%20use%20this%20macro%2C%20see%20attached%20file%20to%20convert%20dates.%20It%20converts%20all%20dates%20in%20selection%20to%20real%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20KonvertDate()%3CBR%20%2F%3EDim%20rngCell%20As%20Range%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20Each%20rngCell%20In%20Selection%3CBR%20%2F%3ErngCell.Value%20%3D%20CDate(rngCell.Value)%3CBR%20%2F%3ENext%20rngCell%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%20from%20germany%3C%2FP%3E%3CP%3EBernd%3C%2FP%3E%3CP%3E%3CA%20title%3D%22VBA-Tanker%22%20href%3D%22http%3A%2F%2Fwww.vba-tanker.com%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.vba-tanker.com%20-%20a%20database%20full%20of%20macros%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677117%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Text%20to%20Date%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677117%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356309%22%20target%3D%22_blank%22%3E%40Nina_SC%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3EYou%20may%20select%20your%20column%2C%20on%20ribbon%20Data-%26gt%3BText%20to%20Columns.%20On%20second%20step%20of%20the%20wizard%20select%20as%20delimiter%20any%20symbol%20which%20is%20not%20appears%20in%20you%20data%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20249px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117434i42592FA863959F62%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EOn%20third%20step%20select%20Date%20and%20MDY%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20248px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117438i99E1924DF6D2663B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIt%20is%20assumed%20your%20default%20date%20format%20is%20m%2Fdd%2Fyyyy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-735702%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Text%20to%20Date%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-735702%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Sergei%2C%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThank%20you%20for%20your%20reply.%20I%20did%20what%20you%20have%20instructed%20but%20still%20no%20visible%20change%20though.%20Is%20there%20another%20workaround%20I%20could%20do%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENina%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-735960%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Text%20to%20Date%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-735960%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356309%22%20target%3D%22_blank%22%3E%40Nina_SC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYour%20issue%20is%20common%20when%20going%20from%20data%20in%20one%20date%20format%20e.g.%20US%20to%20another%20e.g.%20UK%20%2F%20Australian%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20did%20a%20video%20on%20it%20here%20on%20how%20Power%20Query%20in%20Excel%20can%20fix%20it%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.linkedin.com%2Fpulse%2Ffixing-us-date-format-nightmare-wyn-hopkins%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fyoutu.be%2FJL_HpxUfN4Mhttps%3A%2F%2Fwww.linkedin.com%2Fpulse%2Ffixing-us-date-format-nightmare-wyn-hopkins%2F%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-735963%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Text%20to%20Date%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-735963%22%20slang%3D%22en-US%22%3E%3CP%3EThere's%20also%20a%20long%20formula%20solution%20attached..%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-736042%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Text%20to%20Date%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-736042%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356309%22%20target%3D%22_blank%22%3E%40Nina_SC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOne%20extra%20note%20to%20flag%2C%26nbsp%3B%20don't%20open%20then%20save%20your%20CSVs%2C%26nbsp%3B%20use%20Power%20Query%20to%20pull%20the%20data%20in%20without%20opening%20the%20original%20CSVs.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20your%20example%20most%20solutions%20will%20leave%20the%20circled%20values%20as%203rd%20January%2C%203rd%20February%20but%20I'm%20guessing%20the%20original%20dates%20were%201st%20and%202nd%20of%20March%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20508px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122059i1F425835E2D26D3B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-754008%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Text%20to%20Date%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-754008%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3BHi%20wyn%2C%20thank%20you%20so%20much%20for%20your%20suggestion.%20I%20tried%20applying%20it%20and%20used%20power%20query%20in%20power%20bi%20--%20and%20after%20changing%20the%20locale%20still%20nothing%20has%20changed.%20The%20error%20was%20still%20there.%20I%20don't%20know%20where%20the%20error%20is%20coming%20from%20but%20would%20you%20mind%20taking%20a%20look%20at%20my%20file%3F%20It%20would%20mean%20a%20lot%20and%20thank%20you%20so%20much.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-754023%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Text%20to%20Date%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-754023%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356309%22%20target%3D%22_blank%22%3E%40Nina_SC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20Power%20Query%20click%20Data-%26gt%3BFrom%20Table%2FRange%20(it'll%20be%20converted%20to%20table)%2C%20Transform%2C%20in%20Power%20Query%20editor%20select%20all%203%20columns%2C%20and%20from%20right%20click%20menu%20select%20Change%20Type-%26gt%3BUsing%20locale.%20Here%20Date%20and%20English%20(United%20States).%20Land%20the%20result%20back%20to%20the%20sheet.%3C%2FP%3E%0A%3CP%3EGenerate%20script%20is%3C%2FP%3E%0A%3CPRE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20%23%22Changed%20Type%20with%20Locale%22%20%3D%20Table.TransformColumnTypes(Source%2C%20%7B%0A%20%20%20%20%20%20%20%20%7B%22Created%20Time%22%2C%20type%20datetime%7D%2C%0A%20%20%20%20%20%20%20%20%7B%22Last%20Update%20Time%22%2C%20type%20datetime%7D%2C%0A%20%20%20%20%20%20%20%20%7B%22Close%20Time%22%2C%20type%20datetime%7D%7D%0A%20%20%20%20%2C%20%22en-US%22)%0Ain%0A%20%20%20%20%23%22Changed%20Type%20with%20Locale%22%3C%2FPRE%3E%0A%3CP%3EPlease%20see%20Sheet2%20in%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-754184%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Text%20to%20Date%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-754184%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356309%22%20target%3D%22_blank%22%3E%40Nina_SC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUnfortunately%20once%20you've%20saved%20the%20data%20in%20Excel%20all%20of%20those%201st%20of%20June%20dates%20have%20been%20already%20converted%20to%206th%20of%20January%20which%20is%20wrong.%26nbsp%3B%20Power%20Query%20can't%20fix%20this%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20you%20need%20is%20the%20original%20unsaved%20CSV%20file%20and%20then%20use%20Power%20Query%20to%20pull%20that%20data%20in.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20you%20have%20the%20original%20CSV%20you%20can%20post%20here%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-754193%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Text%20to%20Date%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-754193%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356309%22%20target%3D%22_blank%22%3E%40Nina_SC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20don't%20have%20the%20original%20unsaved%20then%20you%20may%20need%20to%20use%20a%20formula%20approach%20like%20the%20attached%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Nina_SC
New Contributor

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? 

10 Replies
Solution

@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

www.vba-tanker.com - a database full of macros

@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

image.png

On third step select Date and MDY

image.png

It is assumed your default date format is m/dd/yyyy

@Sergei Baklan 

 

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

@Nina_SC 

 

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

https://youtu.be/JL_HpxUfN4Mhttps://www.linkedin.com/pulse/fixing-us-date-format-nightmare-wyn-hopki...

 

 

Converting US date format MM DD YY to a DD MM YY format can be incredibly tricky and time consuming in Excel. Until now.... Power Query can do it in a couple of clicks!

There's also a long formula solution attached..

 

 

@Nina_SC 

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

 

image.png

 

 

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

@Nina_SC 

 

With Power Query click Data->From Table/Range (it'll be converted to table), Transform, in Power Query editor select all 3 columns, and from right click menu select Change Type->Using locale. Here Date and English (United States). Land the result back to the sheet.

Generate script is

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {
        {"Created Time", type datetime},
        {"Last Update Time", type datetime},
        {"Close Time", type datetime}}
    , "en-US")
in
    #"Changed Type with Locale"

Please see Sheet2 in attached.

Hi @Nina_SC 

 

Unfortunately once you've saved the data in Excel all of those 1st of June dates have been already converted to 6th of January which is wrong.  Power Query can't fix this

 

What you need is the original unsaved CSV file and then use Power Query to pull that data in.

 

Do you have the original CSV you can post here?

 

 

 

@Nina_SC 

 

If you don't have the original unsaved then you may need to use a formula approach like the attached