Excel Formatting to Date/Time

[lsm33000]

Hello,

I imported a txt file into excel and one column returned date and time like this: 01 Apr 2013 0821 (with spaces). How can I convert that into a format that Excel will then recognize as a date/time value that I can use for formulas? Thank you.

[Pete_UK]

If your data/time is in A1, you can use this formula (e.g. in B1):

=DATEVALUE(LEFT(A1,11))+TIME(MID(A1,13,2),RIGHT(A1,2),0)

Format the cell using a custom format in the style you wish to see it (e.g. mm/dd/yyyy hh:mm)

Hope this helps.

Pete

[FDibbins]

Hi and welcome to the forum

Assuming your data is always in that format, and assuming it is in A1, downwards, use this, copied down...

=DATEVALUE(MID(A1,1,LEN(A1)-5))+TIME(LEFT(RIGHT(A1,4),2),RIGHT(RIGHT(A1,4),2),0)
custome format "dd mmm yyyy h:mm AM/PM"

edit: lol, love it Pete same but different

[lsm33000]

Thank you! It worked!

[lsm33000]

Do either of you have a moment to explain the the commands: (left, mid, right) and (11,13,2). I want to try to understand the concept, so I can do this on my own next time. I have an Excel 2010 bible, but it doesnt quite explain what these commands do. I appreciate your time!

[Pete_UK]

Those act on a string of text to return the LEFT-most characters, or characters from the MIDdle of the string, or the RIGHT-most characters. For LEFT and RIGHT you need to specify the numbers of characters that you need, but for the MID function you need to specify the character number that you want to start with AND the number of characters that you want to extract, so LEFT(A1,11) returns the first 11 characters of the string in A1, and RIGHT(A1,2) returns the final two characters of A1, and MID(A1,13,2) returns 2 characters from A1 starting with the 13th character.

You can find more details (and examples) of these functions in XL Help (press F1).

Hope this helps.

Pete

[Teethless mama]

Try this shorter version:

=--REPLACE(A1,15,,":")

[lsm33000]

Thank you!!

[Teethless mama]

You're Welcome!