Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

miscluce

MIS
Oct 4, 2007
149
US
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?
 
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
 
this is something that is done once a year but they have about 500 numbers to convert
 



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,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Can't you use a custom Format to make it look like a date.
Something like: 00\/00\/00
 
I ALWAYS seem to forget about the "text to columns" deal. [wink]

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

--

"If to err is human, then I must be some kind of human!" -Me
 
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.
 



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,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top