Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Valid Date ? (Excel 2003)

    Hi All ...

    Is there a formula/function that can easily tell if a date is valid? I have a cell (say A4) that uses VLOOKUP and can return 3 possibilities:
    --a real date
    --01/00/00
    --#N/A

    I would like to place a formula/function in a cell (say B4) that returns TRUE if this vlookup returns a real date.

    Thanks,
    --cat

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

    Re: Valid Date ? (Excel 2003)

    What is the 01/00/00? If it is January 0, 1900 Excel still considers it to be a valid date.

    There is no worksheet function that tests for a valid date. You could create a custom function:
    <code>
    Public Function IsADate(ByVal Arg) As Boolean
    Application.Volatile
    On Error GoTo ExitHere
    IsADate = IsDate(Arg) And Arg > 0
    ExitHere:
    End Function
    </code>
    If you put it in a module in the workbook, you can use it like this:
    <code>
    =IsADate(A4)</code>

  3. #3
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Valid Date ? (Excel 2003)

    01/00/00

    is the result of a VLOOKUP that finds the row, but the column that it is pulling is blank but formatted as a date.

    --cat

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

    Re: Valid Date ? (Excel 2003)

    OK, the IsADate function from my previous reply should handle that.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Valid Date ? (Excel 2003)

    Hans: Why does this function need to be Volatile?
    Legare Coleman

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

    Re: Valid Date ? (Excel 2003)

    It doesn't, actually. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    I have a tendency to add Application.Volatile to UDFs as a precaution...
    Thanks!

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Valid Date ? (Excel 2003)

    That tends to create a LOT of overhead! <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    Legare Coleman

Posting Permissions

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