Function to clean up date/timestamp

[jwilds1]

I have date/time data in the following form:

Mon Jan 12 07:26:26 EST 2009

I have tried multiple forms of LEFT, RIGHT, MID, etc. Date & Month are always three characters, but the date ("12" in this example) can vary between 1 or 2 characters.

Any assistance you can offer would be fantastic!!

[squiggler47]

=LEFT(C3,3)
=MID(C3,FIND(" ",C3,3)+1,3)
=MID(C3,9,FIND(" ",C3,9)-8)
=MID(C3,FIND(" ",C3,9)+1,8)
=MID(C3,FIND(" ",C3,17)+1,3)
=RIGHT(C3,4)

Assuming date is in cell c3 should return all the parts!

=DATEVALUE(MID(C3,9,FIND(" ",C3,9)-8)&"-"&MID(C3,FIND(" ",C3,3)+1,3)&"-"&RIGHT(C3,4))
excel date

=TIMEVALUE(MID(C3,FIND(" ",C3,9)+1,8))
excel time

=DATEVALUE(MID(C3,9,FIND(" ",C3,9)-8)&"-"&MID(C3,FIND(" ",C3,3)+1,3)&"-"&RIGHT(C3,4))+TIMEVALUE(MID(C3,FIND(" ",C3,9)+1,8))
excel date & Time

[jwilds1]

Thanks, but I'm getting #VALUE error in the FIND function. I've tried changing the FIND to SEARCH and still getting it. Any advice?

[squiggler47]

you might be better posting a worksheet with a few time stamps in it from your sheet, I could only work of the example you gave me, and the spaces may not be real spaces!

It worked for me when I pasted the exampel you gave into a blank sheet!

[DonkeyOte]

An adaptation of squiggler's approach based on the fact that all apart from day are seemingly regimented:

Please Login or Register  to view this content.

Where

A1: Mon Jan 12 07:26:26 EST 2009

should also work if day is 1 char or 2.

[jwilds1]

I've attached a file with the example timestamps.

DonkeyOte - that worked, but I would also like the actual times. The goal of this is to subtract two timestamps, eventually coming up with a time between each entry (minutes, seconds).

Thanks!!!!

[DonkeyOte]

Not sure I follow, the formula I provided converts the string to a datetime value. You will however need to format the cell(s) containing the formulae to show the datetime in full (ie dd/mm/yyyy hh:mm:ss).

[squiggler47]

Ok, you just needed to change the C3 to A2

Here is datestamp reproduced with a custom format, it is now an excel time/date so can be formatted to any layout (examples in next 4 cols!

[jwilds1]

Thanks to both of you. I was in such a hurry I missed changing the formatting of DonkeyOte's solution.