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

    Thumbs up

    Hi,

    Excel has ISNUMBER and ISTEXT functions. But if I need to check a cell for dates, how do I do this? Is there any better way than just checking if its NOT a number or text?

    TX
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    See for example the topic Test cell content for data-type!! (Excel 2000>), or use this function from another post:

    Public Function IsADate(ByVal Arg) As Boolean
    On Error GoTo ExitHere
    IsADate = IsDate(Arg) And Arg > 0
    ExitHere:
    End Function

    If you put it in a module in the workbook, you can use it like this:

    =IsADate(A4)

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

    Wow... this is weird that it must revert to a UDF. They must have forgotten to build in a function when designing the appl.

    Cheers for the links.
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'll await the next time you ask this question, in 2011.

  5. #5
    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
    It seems to need a UDF since you need to test that the cell contains a number within a certain range AND it must also be formatted with a date format.

    I don't think there are any builtin functions to check the number format of a cell...

    The only think that distinguishes most numbers from a date is the defined format of the cell. If the format of the cell is not a date the UDF will not see it as a date....

    Steve

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    You are quite right Steve. That is a good explanation why there is no ISDATE! A date is actually a formatted number, so this would be covered by ISNUMBER. This makes sense. TX.
    Regards,
    Rudi

  7. #7
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='Rudi' post='763951' date='06-Mar-2009 19:57']You are quite right Steve. That is a good explanation why there is no ISDATE! A date is actually a formatted number, so this would be covered by ISNUMBER. This makes sense. TX.[/quote]
    You could try a combination of ISNUMBER(), > 0, and =CELL("format",) to find cells that are non zero and formatted as dates.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='763714' date='05-Mar-2009 16:58']The only think that distinguishes most numbers from a date is the defined format of the cell.[/quote]
    "Good Call!", if you'll excuse the pun!

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

    This is a good suggestion, but in testing your theory, I noticed that the cell("format") option only tests for dates in a certain format as you can see in the help file and the formula.

    [attachment=82710:Capture.JPG]
    Attached Images Attached Images
    Regards,
    Rudi

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Rudi' post='764037' date='07-Mar-2009 13:50'][/quote]
    Try

    =AND(ISNUMBER(A1),LEFT(CELL("format",A1),1)="D")

    There's no need to use IF(condition,TRUE,FALSE), since this is equivalent to =condition.

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Hans. This does not work unfortunately as the cell function returns the standard "G" (General) for any dates that do not match the formats listed in the help file.

    PS: Since a solution is posted earlier in this thread, these are just "experiments". But it would be interesting to see if there is another solution!
    Regards,
    Rudi

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Rudi' post='764040' date='07-Mar-2009 14:00'][/quote]
    I don't think there's a really satisfactory non-VBA solution (using only built-in functions). If there is a non-VBA solution at all, it will be much more complicated than the relatively simple user-defined function posted higher up in this thread.

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

    TX
    Regards,
    Rudi

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    [quote name='Rudi' post='764040' date='07-Mar-2009 13:00']Hi Hans. This does not work unfortunately as the cell function returns the standard "G" (General) for any dates that do not match the formats listed in the help file.

    PS: Since a solution is posted earlier in this thread, these are just "experiments". But it would be interesting to see if there is another solution! [/quote]

    You could use a name defined like =GET.CELL(7,A1) to get the actual number format, but again it's going to get very messy!
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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