Results 1 to 10 of 10

Is there an "ISDATE" type function in Excel

This is a discussion on Is there an "ISDATE" type function in Excel within the Excel Questions forums, part of the Question Forums category; Is there any such function within Excel that will allow me to check for a date in a column that ...

  1. #1
    New Member
    Join Date
    Jan 2003
    Posts
    4

    Default


    Is there any such function within Excel that will allow me to check for a date in a column that I can use.

    I looking for something like the ISTEXT type command that would return a TRUE value if the condition of a date is returned.

    If anyone else has a easy solution, I would appreciate it.

    Regards

  2. #2
    MrExcel MVP phantom1975's Avatar
    Join Date
    Jun 2002
    Location
    Omaha, Nebraska
    Posts
    3,962

    Default

    WELCOME TO THE BOARD!

    Here is a UDF that will do the trick:

    Function IsDate(CellDate As Date) As Boolean
    If CellDate >= 1 And CellDate <= #12/31/2199# Then
    ' 1 is equal to January 1, 1900
    IsDate = True
    Else
    IsDate = False
    End If
    End Function


    _________________
    ***************** EXCEL/VB NEWBIES ARE MY FAVORITE! ****************

    [ This Message was edited by: phantom1975 on 2003-02-17 22:21 ]

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    Hi ru4ward:

    As phantom1975 has delineated, ISDATE function is available in VBA, but not in native Excel. The following is a formula based approach to check whether the entry in cell B3 is a valid date:

    '=IF(NOT(ISERROR(DATEVALUE(TEXT(B3,"mm/dd/yyyy")))),"valid date","invalid date")

    Please post back if it works for you -- otherwise explain a little further and let us take it from there.


    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    63,674

    Default

    On 2003-01-29 23:41, ru4ward wrote:

    Is there any such function within Excel that will allow me to check for a date in a column that I can use.

    I looking for something like the ISTEXT type command that would return a TRUE value if the condition of a date is returned.

    If anyone else has a easy solution, I would appreciate it.

    Regards
    =OR(CELL("format",A1)={"D1","D2","D3","D4","D5"})

  5. #5
    New Member
    Join Date
    Dec 2009
    Posts
    4

    Default Re: Is there an "ISDATE" type function in Excel

    I inserted the VBA Function above, but it doesn't handle ranges.

    Is there one that would more affectively handle ranges and being inserted into and used in a fashion like the following:

    =COUNTIFS((C3:C147), "InActive", (F3:F147),(ISDATE(ROWS(F3:F147))=FALSE))

    Or of course correcting my syntax or suggesting another way to perform this function properly.

    I am using Excel 2007.

    What I need to accomplish:
    A) Text in Rows C3:C147 must be "Inactive"
    B) & if active count the number who have are so old we do not have a VALID transaction DATE in the system.


    Thanks:
    Christopher
    Last edited by crsouser; Dec 23rd, 2009 at 03:37 PM. Reason: Added version of Excel I am using.

  6. #6
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,664

    Default Re: Is there an "ISDATE" type function in Excel

    Hi Christopher

    What would constitute a valid transaction date? Assuming the column will either contain blanks or a valid transaction date you could perhaps use:

    =COUNTIFS(C3:C147,"Active",F3:F147,">0")

    if the specification for a valid transaction date is more complicated there will be alternatives.
    Richard Schollar

    Using xl2013

  7. #7
    New Member
    Join Date
    Dec 2009
    Posts
    4

    Default Re: Is there an "ISDATE" type function in Excel

    Quote Originally Posted by RichardSchollar View Post

    =COUNTIFS(C3:C147,"Active",F3:F147,">0")

    if the specification for a valid transaction date is more complicated there will be alternatives.
    Unfortunately I do need to validate that it is indeed a valid date as many of the fields have other data in them.. some blank, some 000s, some where people decided to use the field as a memo field as it wasn't enforced, etcetra.

    So I guess I am looking for the more complicated version, even if VB script

    Christopher

  8. #8
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,262

    Default Re: Is there an "ISDATE" type function in Excel

    Hi Christopher

    You did not answer Richard's question.

    Quote Originally Posted by RichardSchollar View Post
    What would constitute a valid transaction date?
    Is it a text with a date in international format "yyyy-mm-dd" or is a text with some regional format like "dd-mm-yy" or "mm/dd/yyyy", does it have a fixed number of digits like "01-02-2009" or can you write it without the zeros like "1-2-2009", etc.

    Please clarify.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  9. #9
    New Member
    Join Date
    Dec 2009
    Posts
    4

    Default Re: Is there an "ISDATE" type function in Excel

    A valid date being at least "MM-DD-YYYY", if it could handle more than one format even better (i.e. MM/DD/YYYY, MM/DD/YY, etc)

    Christopher

  10. #10
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,262

    Default Re: Is there an "ISDATE" type function in Excel

    In that case you may start by using the vba IsDate(). Although not bulletproof it's maybe enough for your test.

    This UDF returns an array with the results of the tests for date in the cells of a vertical contiguous vector.

    Code:
    Function IsValidDate(rDates As Range)
    Dim j As Long, vDates
     
    ReDim vDates(1 To rDates.Count, 1 To 1)
    For j = 1 To rDates.Count
        vDates(j, 1) = IsDate(rDates(j).Text)
    Next j
    IsValidDate = vDates
    End Function
    You can use it in the worksheet like:

    =SUMPRODUCT((C3:C147="Active")*IsValidDate(F3:F147))

    to get the number of rows with column C equal to "Active" and with a valid date in column F.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com