Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    Regards,
    Rudi

  2. #2
    Star Lounger
    Join Date
    May 2005
    Location
    west drayton, Middlesex, United Kingdom
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make a date! (Excel 2002)

    Hi Rudi

    Anything at this link help you

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

    Cheers

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Make a date! (Excel 2002)

    <P ID="edit" class=small>(Edited by sdckapr on 12-May-05 09:38. Added PS)</P>How about this:
    =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. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    Regards,
    Rudi

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    Regards,
    Rudi

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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
  •