Solved: Cell Text Formatting - Irregular Text Date to Date Value

[MisterNate]

Hi.

I've got a mess of a file that I have to use - some data dump that submits in .csv format, but the dates won't convert to a date value.

Here's two examples of how the dates come:

xxxxxxxxOctxx8x2010xx7:00AMx

OR

xxxxxxxxOctx10x2010x10:00AMx

X's here represent blanks in the file. The number of spaces are the same at the beginning of the field, but then they vary from there.

Anyone have an idea of a formula that I can insert for all rows to convert this into a date value?

Any help would be hugely appreciated...

Thanks!!!

Nate.

[RonMcK]

Nate,

I love a wee challenge. The formula you need is below and in the accompanying worksheet. Notice that it uses a table to convert the month text to a number for the DATE() function and assumes that all months are 3-char codes.[vba]=DATE(VALUE(RIGHT(LEFT(TRIM(A10),11),4)),
VLOOKUP(LEFT(LEFT(TRIM(A10),12),3),cnvMonths,2,FALSE),
VALUE(TRIM(MID(LEFT(TRIM(A10),12),5,2)))
)+TIMEVALUE(TRIM(LEFT(RIGHT(TRIM(A10),7),5)))
+0.5*("PM"=RIGHT(TRIM(A10),2))[/vba] To use all of this:

1. Insert a column to the right of your date column;
2. Copy the cnvMonths table to a convenient place on your worksheet;
3. Highlight the table range (1st month name upper left to last month number lower right);
4. Insert the table name (cnvMonths) using Insert > Name > Define (XL2003/4 and earlier or Name Manager on Formula ribbon in XL2007);
5. Copy formula to your worksheet, pasting it in the new column;
6. Highlight all cells in column for rows needing date fixed.
7. Fill Down (Edit > Fill > Down)

XLD probably has a much simpler and more elegant formula for us.

[macropod]

Seems to me that it would be far simpler to use:
=--SUBSTITUTE(SUBSTITUTE(TRIM(A1),"AM"," AM"),"PM"," PM")
and custom format the cell as a Date & Time

[RonMcK]

macropod,

Thanks. I just knew I'd smoke a guru out of the underbrush.

The nested substitutes are working for me but when I add the "--" things fail. I gather "--" is supposed to coax Excel into converting the time as a text string into a numeric value (julian number plus time of date decimal). Anyway, my cell with your formula displays "#VALUE!" instead of converted date/time.

Cheers,

[MisterNate]

Yes, when converting to a value using Mac's formula, I get the same thing...

Ron, tried yours, and couldn't make the reference work...any chance you could send me the source file so I could look at how you did it?

Email is nwendel.work@gmail.com

Thanks!!!

[RonMcK]

You should be able to download it since it's attached to #2, above.

Cheers!

[macropod]

The nested substitutes are working for me but when I add the "--" things fail. I gather "--" is supposed to coax Excel into converting the time as a text string into a numeric value (julian number plus time of date decimal). Anyway, my cell with your formula displays "#VALUE!" instead of converted date/time.

Hmm, it works with a UK/AUS date format (provided you have UK/AUS regional settings). Back to the drawing board with that one, I guess.

[Mikey]

This version should work irrespective of regional settings

=REPLACE(MID(TRIM(REPLACE(A1,FIND(":",A1)+3,0," ")),5,99),3,0,"-"&LEFT(TRIM(A1),3)&"-")+0

[RonMcK]

Mikey,

Bingo! Thank you very much, and, as you predicted, this works fine on my XL2007, XL2004, and XL2008.

Nate, I trust that you're still reading these msgs; this is your answer.

Cheers, one and all!

[Bob Phillips]

Hmm, it works with a UK/AUS date format (provided you have UK/AUS regional settings). Back to the drawing board with that one, I guess.

Not here it doesn't.

[RonMcK]

Not here it doesn't.

Looking at a different solution, does Mikey's solution (#8, above) work for you?

Thanks,

[Bob Phillips]

Oh yes, that works.

[MisterNate]

Guys, thank you so much!

My apologies for taking so long to respond...fell into an SAP related black hole at work for the last couple weeks...implementation time is soooo fun.

Thanks again for the help...now I'll sit and try to disect it to understand exactly how that worked. In the meantime, the tool that I'm writing will benefit greatly!

Cheers!

[Bob Phillips]

My apologies for taking so long to respond...fell into an SAP related black hole at work for the last couple weeks...implementation time is soooo fun.

I have mates who have never extracted themselves from one of those!

[MisterNate]

haha...oh, believe me, I know. I think that you never fully do. We cut over in May, and to date all I get are the occasional side projects to shore up what SAP can't do (actually, that's more than occasional) and the rare breath of air to see if my family is still speaking to me...I've given my wife permission to see other people...life's great! haha.