Results 1 to 3 of 3
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Count if date (Excel 97-->)

    In column A/Sheet1 I have 65536 rows of data which are in dd/mm/yy format. I am preparing a HealthCheck/Quality workbook for a major data migration exercise. One of the things I have in my test plan is to ensure all of the data in Column A has data in the dd/mm/yy format.

    I have tried some ideas for formulas like the countif, cell but can't get it to work. I want to count the number of cells that do no have it in this format, I would prefer to do this formulaically, not in VBA, any ideas?
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count if date (Excel 97-->)

    I don't know if this is entirely possible without VBA. The following comes near:

    Insert a column for intermediary formulas, say column B.
    In B1, enter the formula =CELL("format",A1).
    Fill down to B65536.
    If the cell format is dd/mm/yy or d/mmm/yyyy or something like that, the result of the formula will be D1. so you can use
    =COUNTIF(B1:B100,"<>D1")
    to count the cells that do not have a DMY-like format.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count if date (Excel 97-->)

    Thanks Hans

    This is exactly how I tried in my initial set up of the workbook. I am not happy for the users to start adding columns to the dataset that will be imported as this is an iterative process for table appends and over rights via Enterprise Manager and this method would create error creep later down the process map if rogue fields are added.

    However having played around with this formula I have come up with the idea of creating a hidden worksheet that has the formula
    =CELL("format",Import!A1), for cell A1 etc downwards in sheet Import in Colomn A. The third sheet would be my Health Check reponse using the Countif formula relating to the values found in Import sheet column A.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

Posting Permissions

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