Formatting Dates

[Guest]

I have a column of dates. These dates are stored in the cell as "112482" and
the cell is formatted as "00\/00\/00" so that in the cell it looks like:
"11/24/82".

The problem is, I can't sort these dates because they are not stored
correctly in the cell for Excel to recognize them like it should. For
instance, "112482" is seen by Excel as 112,482nd day after January 1st, 1900
or 12/18/2007.

It appears there is no function in Excel currently to format my dates
correctly so it can be sorted by Excel. Can I create one using VBA? What
would it be?

 

[Guest]

In a helper column format as DATE

=DATE(RIGHT(A1,2),LEFT(A1,2),MID(A1,3,2))

 

[David Biddulph]

Formatting doesn't change the value, it only changes the display.
You'll need to convert the value, for example using
=--TEXT(A1,"00\/00\/00"). You can then sort by the new column.

 

[Guest]

If you don't mind converting those values directly to dates....

Try this:
Select the vertical range of values

From the Excel main menu:
<data><text-to-columns>
....click [Next] until Step 3 of 3
Set the "Column data format" to: Date MDY
Click [Finish]

Example:
These values
112482
60107
123105

Become these:
11/24/1982
6/1/2007
12/31/2005

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Gord Dibben]

Select the column with the numbers.

Data>Text to columns>Next>Next>Column Data Format>Date>MDY>Finish


Gord Dibben MS Excel MVP

 

[Guest]

That works. Here is a supplemental question for you:

The function works for dates that have double digit months. Single digit
months (January - September) don't have a "0" in front of the month making
the formula return the wrong date.

Example:

4/17/07 is in the spreadsheet as "41707". Due to the cell format, a "0" is
automatically added. Using =DATE(RIGHT(A1,2),LEFT(A1,2),MID(A1,3,2)) in a
date formatted cell gives me "07/09/10". If I add a "0" in front of "41707",
it gives me the correct result but since the spreadsheet has about 5,000
rows, I can't readily devote that much time.

Thoughts?

 

[Guest]

To easily simulate a helper column function..

With A1: A3 containing these values
112482
60107
123105

Select A1:A3
<data><text-to-columns>
....click [Next] until Step 3 of 3
Set the "Column data format" to: Date MDY
Set Destination to: C1
Click [Finish]

The values will be converted to dates beginning in cell C1, without writing
over the original data.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Rick Rothstein \(MVP - VB\)]

That works. Here is a supplemental question for you:

The function works for dates that have double digit months. Single digit
months (January - September) don't have a "0" in front of the month making
the formula return the wrong date.

Example:

4/17/07 is in the spreadsheet as "41707". Due to the cell format, a "0"
is
automatically added. Using =DATE(RIGHT(A1,2),LEFT(A1,2),MID(A1,3,2)) in a
date formatted cell gives me "07/09/10". If I add a "0" in front of
"41707",
it gives me the correct result but since the spreadsheet has about 5,000
rows, I can't readily devote that much time.

This should work for you...

=DATE(YEAR("January 1, " &
RIGHT(A1,2)),LEFT(A1,LEN(A1)-4),MID(A1,LEN(A1)-3,2))

Rick