Conversion from Number to Date

[tayyabq8]

Hi,

I have imported data into Excel from Notepad where one column contatains such a number value "20020901", I want to convert this number to a date format like this "01/09/2002".

Any ideas....


Regards,

Muhammad Tayyab

 

[Blorf]

Assuming you have your value 20020901 in cell a1, try this in another cell.

=MID(A1,5,2) & "/" & RIGHT(A1,2) & "/" & LEFT(A1,4)

Hope that helps,
ChaZ

 

[TonyJollans]

Hi tayyabq8,

Can you not convert it on the import so that it comes in as a date to start with? On the import wizard/dialog say it's a date, formay YMD.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[tayyabq8]

Hi Chaz,

Your trick really did the magic for me, this will help me a lot in future.

Thanks and Best Regards,

Muhammad Tayyab

 

[KenWright]

Muhammad, you are ending up aith a text string and not a real date, and whilst this may satisfy your current need, it is not really good practice. Personally i would have simply used the method that Tony outlined during the import because it is all done for you and you don't have to do any manual conversion, but if you really want to use formulas then why not use them to create a real date, which you can then do real date arithmetic or lookups on etc.

Taking the data from Chaz's formula and simply using it to feed a date formula:-

=DATE(--LEFT(A1,4),--MID(A1,5,2),--RIGHT(A1,2))

will give you a real date to work with.

Only thing iwasn't sure about was whether your 01 was the day or the month, because it is different for various countries. I have assumed the 01 is the DAY, but if indeed it was the mnonth then you would simply amend the formula to:-

=DATE(--LEFT(A1,4),--RIGHT(A1,2),--MID(A1,5,2))

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------