text to timestamp conversion

[nivn]

Hi Friends,

Can you please let me know how to convert 19MAR13:14:16:36 into yyyy-mm-dd hh:mm:ss format. I have both 2007 and 2010 versions

Thanks in advance

Regards,
Nive

[benishiryo]

hi Nive, welcome to the forum. try:
=--LEFT(A1,FIND(":",A1)-1)+MID(A1,FIND(":",A1)+1,LEN(A1))

then format cell as you desire

[Ron Coderre]

With your text
A1: 19MAR13:14:16:36

This regular formula converts it to an Excel Date/Time value

Please Login or Register  to view this content.

Format that cell the way you want:
• Select the cells to be formatted
• CTRL+1...to open the formatting window
...Category: custom
...Type: yyyy-mm-dd hh:mm:ss
...Click: Ok

Is that something you can work with?