Text to Date 1

[TAngel]

Hi All,
I have a csv file that is combined with three other excel spreadsheets and turned into a report. One of the columns has the text field that is really a date value; example: 41008 for April 10, 2008.

I need to change this to a date and display as 10-Apr-08.

Any recommendations? Using Date Format doesn't work since the text series isn't the correct value.

TIA
Tee

 

[AnotherHiggins]

What would April 8 look like?

I'm afraid that your answer will be 4808. That's terrible news - and a terrible format for the producer of the csv to be using.

That format is mdyy. If the format was mmddyy (always displaying two digits for month and day), then this would be quite simple. But with the format mdyy, then I'm afraid this will be [!]impossible[/!].

Why impossible? Consider 11108. You cannot look at that and determine if it represents 11-Jan-08 or 1-Nov-08. Any chance of getting the format changed in the source?

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[TAngel]

John, When I open the csv file in notepad I can see the 040108, however, when opened in excel the leading zero is dropping off. So, the source does have mmddyy until I open it in excel.

 

[SkipVought]

Hi,

Select your column of dates.

Data > Text to columns...

Either delimited or Fixed

NEXT NEXT

Select Date - MDY

FINISH

See if that might work for you.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[zbnet]

Assuming your 5 or 6 digit 'text date' is in A1, and assuming all of the 2-digit years are after 1999, you can use this formula in one of the other colums to translate it into an Excel date field:

=DATE(2000+RIGHT(A1,2),LEFT(A1,LEN(A1)-4),MID(A1,LEN(A1)-3,2))

 

[TAngel]

Thanks Skip, that worked.

 

[richf007]

I have a similar problem where our vendor's MS Excel report has text dates formatted as, for example, 7022002 and 11212004, which translate to 7/02/2002 and 11/21/2004 respectively. My client cannot sort by this date field which is causing them problems with their data analysis. I tried zbnet's suggestion and it worked like a charm resulting in perfect dates that my client can sort.

Many, many thanks zbnet!

Here is the formula I used, where the date field is in column H on the spreadsheet:

=date(right(h2,4),left(h2,len(h2)-6),mid(h2,len(h2)-5,2))

The first row with data is row 2. I inserted a new column after column H, then entered the formula above into cell I2. Next, I simply copied that formula from I2 through to the last row of column I and all dates in the new column were correct.

 

[zbnet]

It's great that you understood the formula well enough to be able to modify it and get it working against your data.