Transform Date 140688 into date format 14.06.88

[inug]

Hi! In an excel table I have thousands of dates in the form "140688". In order to use them for calculations they have to be transformed into a format that excel recognises as "date" (e.g. "14.06.88"). Is there a function that can do this?

[TMS]

Select the column of "dates"
Select Data | Text to columns
Click Next
Click Next
Choose Column data format: DMY
Click Finish
Widen the column if necessary
Format as required

Done


Regards, TMS

[inug]

Thanks a lot for the answer, Guru. Unfortunately I don't quite understand it. Attached is an example (" Example dates.xls"). would it be possible for you to perform the transformation on this example? (if possible with explanations above or below the columns).

[Fahads_neo]

File attached. Do exactly what TMS said.

[Marcol]

Using a formula
In B2, Drag/Fill Down

Please Login or Register  to view this content.

[TMS]

Not really sure what's not to understand ... they're manual steps, select the cells, etc ... In your example, column A ... and follow the instructions:

And trust me, I didn't do that manually, or with a formula.


@Fahads_neo: thanks for the confirmation ... sometimes you start to doubt yourself


Regards, TMS

[inug]

Wow! Many thanks to you all, TMShucks, Fahads_neo, and Marcol! That's been great help. As to not understanding... well, there are as it seems still commands I've never come across or needed to use. Good to have helpful friends out there on the cyber!

[inug]

Dear TMShucks, Funny, the topic I am working on is called TMS (a German acronym). Anyway, the command text to columns was not activated in my Excel, so I didn't know it. I've now solved the problem and it works perfectly. So again, cheers for your help and patience

[TMS]

You're welcome. Thanks for the rep.

I hope it's an acronym for something good

Not activated? I've never come across that. However, I always tend to go for a full installation, so perhaps that explains it.

Anyway, glad you have a solution.

Regards, TMS

[thameem127]

Hi! In an excel table I have thousands of dates in the form "140688". In order to use them for calculations they have to be transformed into a format that excel recognises as "date" (e.g. "14.06.88"). Is there a function that can do this?

If u not satisfied try this formula

=TEXT(A1,"0000-00-00")+0

Format the cell containing this formula with an appropriate date format.

[inug]

Just a note not on Excel but on excellent....

There's an Israeli who used to live in Scotland and was, long ago, a great fan of Georgie Best. He places a question in a forum and gets prompt help from Manchester, Fife, Dubai and Saudi Arabia. Isn't that fantastic? Imagine all the people....

Love to you all,
Inug