Date Format (should be easy!)

[msk7777]

Sorry, I have been search through the database for help on this but I can't seem to find what I am looking for. Then again, I may be searching incorrectly. I have a huge list in a column of file names (example: 11011_ABCclaim). The first part of the name (11011) is actually a date (1/10/2011). I have already done text to columns to get the date from the file name, but now need to convert the number into an actual date format.

Simple formatting of the cells won't give the correct result. So is there an easy way to simply insert a "/" after the 1st and 3rd character?

Thanks for the help!

Msk7777

 

[kpark91]

Other people might come up with bettter solutiosn so check back as I know this is not the most optimal solution:
=IF(LEN(A1) = 5, LEFT(A1, 1) & "/" & MID(A1, 2, 2) & "/" & RIGHT(A1, 2), IF(LEN(A1) = 6, LEFT(A1, 2) & "/" & MID(A1, 2, 2) & "/" & RIGHT(A1, 2)))

where A1 is your date from the file name.

 

[AlphaFrog]

In your date 1/10/11, is the "1/" the month or the day?

A date can have 4 to 6 digits e.g.;
1111 = 1/1/11
11111 = 1/11/11 or 11/1/11
111111 = 11/11/11

If your value has four or six digits, that would be easy to convert into a date. If your value has 5 digits, it could be two different dates. How do you know which is correct?

 

[Rick Rothstein]

Use this formula and format the cell as Date (using a date format of your liking)...

=--(LEFT(A1,LEN(A1)-4)&"/"&MID(A1,LEN(A1)-3,2)&"/"&RIGHT(A1,2))

 

[Rick Rothstein]

Here is a better formula than the one I posted originally...

=--TEXT(A1,"0\/00\/00")

You will still need to apply the cell format of your liking to the cell.

 

[msk7777]

Thanks guys, the second formula worked perfectly. I truely appreciate the help, saved me hours of manual entry!

Msk7777