# Thread: Make a date! (Excel 2002)

1. ## Make a date! (Excel 2002)

I have values such as 234, 127, 2710, 511, 912, 1411, 173, etc running down the A column from row 5. The numbers represent d/m dates. I need your help to convert these into valid dates in the B column that I can use for charting purposes and other date based calcs! The current year (2005 - typed in B2) will be attached to this to eventually read, eg. 23/04/2005.

TIA

2. ## Re: Make a date! (Excel 2002)

Hi Rudi

http://support.microsoft.com/default.aspx?...N-US;q214094#13

Cheers

3. ## Re: Make a date! (Excel 2002)

=IF(LEN(A5)=4,DATE(\$B\$2,RIGHT(A5,2),LEFT(A5,2)),IF (LEN(A5)=2,DATE(\$B\$2,RIGHT(A5,1),LEFT(A5,1)),IF(LE N(A5)=3,IF(VALUE(RIGHT(A5,2))<=12,DATE(\$B\$2,RIGHT( A5,2),LEFT(A5,1)),DATE(\$B\$2,RIGHT(A5,1),LEFT(A5,2) )),NA())))

Steve
PS
I assume something like 211 is Nov 2 not Jan 21. You will have to check for these which are not clear. I check for a "valid month" in the last 2...

4. ## Re: Make a date! (Excel 2002)

Thanx...It literally list all else but that which I need. I need to take a number like 123 and change it to 12/03/2005. So I suspect that I may need to use LEFT to extract the 12, and RIGHT to extract the 3. Then throw them into DAY and MONTH to generate Serial numbers. Then create the date using DATE...etc. I have not succeeded yet!
I may be approaching this the long way round too! So I have posted it to find out if the guru's have an easier way!

Thanx for the response though!
Cheers

5. ## Re: Make a date! (Excel 2002)

Steve...it looks to be working GREAT!!!
See the attached!

I tested it with the other entries I have, and it is doing the job! Its quite some formula! Tx

6. ## Re: Make a date! (Excel 2002)

As mentioned the len=2 and len=4 should be unique. Many of the len3 are unique. There will be some in Jan and Feb:
111 is considered Nov 1, not Jan 11.
211 is considered Nov 2, not Jan 21.
311 is considered Nov 3, not Jan 31.

112 is considered Dec 1, not Feb 11.
212 is considered Dec 2, not Feb 21.

I can think of no way to distinguish them without additional info...

I think other than these 5, the others should be okay...

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•