converting numbers to date

rekless44

New Member
Joined
Sep 25, 2002
Messages
2
i'm looking to convert a number to a date so i can sort by date. for instance, if i have a number like 101502. how do i get it to be recognized as 10-15-02. the only way i could get it to look like that was to go to format cells-custom, and type in ##-##-##. This works, but when i sort by date, it won't go from latest date to most current. if this is possible, i could use all the help i can get. thanks.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
consider using text to columns.

Select it from the date menu, hit next, next again, select the date radio dial with you mdy format, and finally hit finish.

If you always use a two digit day you should be fine, otherwise beware of dates like
11102. This will be picked up as January 11.
 

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
Is your "number" really a number? If it is a cell value, I think it's a variant, right?

You could use Mid() to insert your chosen delimiters, and then reassign the new string to .Value.

This technique may be sensitive to how the cell is formatted.

Once it looks like a date, do you want it to have a serial value, like a "real" Excel date?
 

Forum statistics

Threads
1,144,739
Messages
5,726,006
Members
422,652
Latest member
Elnene1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top