trying to convert a mixed up numbere to a date in excel 1

[miscluce]

i ma trying to convert numbers to a date but its not working.

11888 should be 01/18/88
51208 should be 05/12/08

but I am getting this respectively...

07/18/32
03/13/40


any ideas?

 

[kjv1611]

If you're just changing the format, then it's probably viewing them as Julian dates is my guess.

What you need is a sort of complex formula(or in other words, functions embedded in a main formula, or to do it in VBA.

Do you have any preference? I think the best way would be to do it in VBA, and if it's something you do all the time, you could create your own custom Formula in VBA, and then just reference that in the Excel cells.

Like this: Cell A1 - enter "=MyNewFormula(CellReferences)"

If you want to try any of the VBA routes, then you might want to move your question over to forum707.

If not, then we can go on with getting the formula right in Excel. I think you can use a few formulas such as IIF, Left, Right, etc to get the right splits, but I think it'll be quite a long formula.

--

"If to err is human, then I must be some kind of human!" -Me

 

[miscluce]

this is something that is done once a year but they have about 500 numbers to convert

 

[SkipVought]

Hi,

No need for VBA.

First understand Date/Time. faq68-5827

Then understand that what you have are not really NUMBERS. They are TEXT, with numeric characters that represent date digits in MDY order.

Select the entire column

Data > Text to columns - [NEXT][NEXT] - Select the Column data format for DATE as MDY

VOLA!



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LearningAsIGo]

Can't you use a custom Format to make it look like a date.
Something like: 00\/00\/00

 

[kjv1611]

I ALWAYS seem to forget about the "text to columns" deal.

I'd go with Skip's suggestion fer sure!

--

"If to err is human, then I must be some kind of human!" -Me

 

[AnotherHiggins]

LAIG: Then you're left with TEXT that looks like a date. It's much better to have a date. Storing it as text severely limits your analysis and reporting capabilities.

Text is a fine way to store a part number or id number - something that you will never treat as an actual number. But dates should be stored as dates.

[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.

 

[miscluce]

Yeah, teh test to column thing worked. thanks.

 

[SkipVought]

LearningAsIGo,

Seems like you're from a mainframe background. Mainframe programs like COBOL, have special ways of dealing with numeric values, as you know and dates in particular. It's a bit different with VB and the like. You really ought to read the FAQ I posted above.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}