Results 1 to 4 of 4

Thread: Matching Dates

  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Col A in the attached spreadsheet contains a list of birth dates given to me, Col F contains a list of dates extracted from our database. I need to match each value in Col A with a value in Col F but, as you can see from my attempts to use Match (Col C) are not working.

    I think it involves the formatting of the values but I cannot work out how, I have tried pasting formats from Col A to Col F and vice versa, and using Text(A1, "mmm dd yyyy") but no success.

    Can Anyone help?

    TIA
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The values in F are text representations of dates. The formatting will not matter as Excel will only see them as text until they are altered to true dates.

    You can use this formula to convert the yext to date:
    =--(SUBSTITUTE(TRIM(F2)," ",", ",2))
    Copy down as far as needed
    Then format the cells to the date format of your choice.

    Adjust your match() function to use the column with the formulas.

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    This also works for me

    Create a New Date Column in G from G2

    =DATEVALUE(VALUE(MID(F2,5,2)) & "-" & LEFT(F2,3) & "-" & RIGHT(F2,4))

    Copy dowm and then change Formula in B

    =MATCH(A2,$G$2:$G$58,0)



    Andrew

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Manually:

    - Do a search and replace on your offending dates column, replace two spaces with one
    - Then replace space with - or /
    - Finally, select the offending column, Data, Text to columns, fixed width, Push Next (twice) and set up the format for the field as being MDY. Finish.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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