Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Imported Dates (2003)

    I import a lot of Databases made up of names and DOB dates. The birthdates are 9/10/06, 9/10/26. When I format the cells to give me dd,mm,yyyy. I get 09/10/2006 and 09/10/2026. I know the 2026 should be 1926 but Iím never sure about the 2006 it could be 1906. Is there away to fix this. Maybe before I import I can change some setting or something?

  2. #2
    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: Imported Dates (2003)

    The best way would be to have the source be explicit and list the dates with all 4 digits and avoid the assumptions that XL makes in converting years of 2 digits.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Imported Dates (2003)

    What is the source of the input database? Can you specify the format as yyyy? If not, there's a way to fix the obvious ones, but the others you'll never be sure of. Here's one way to do it. I'm sure there is a more efficient macro that someone else can provide.
    Suppose the birthday 09/10/2026 is in column A2. I would make 3 columns - month, day and year in columns B, C, and D. Extract the month in B2 using =month(A2)
    Extract the day in C2 using =Day(A2). Extract the year in D2 using =IF(YEAR(A2)>YEAR(TODAY()),YEAR(A2)-100,YEAR(A2)).
    Finally in Column E you can have your "good" date: =DATE(D2,B2,C2).
    You can do this in one column, but I've made it 4 columns for ease of understanding.


    I've attached a brief example.
    Attached Files Attached Files

  4. #4
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Imported Dates (2003)

    Yes, this is a tedious chore.
    If all you want is the person's age, if the DoB is in B2, then this formula works:
    =IF((TODAY()-B2)/365.25<0,(TODAY()-B2)/365.25+100,(TODAY()-B2)/365.25)
    It detects the negative age quoted if the year of birth is returned as eg 2026, and adds 100 years to it.
    JRR

Posting Permissions

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