Is there an "ISDATE" type function in Excel

[ru4ward]

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

 

[phantom1975]

WELCOME TO THE BOARD!

Here is a UDF that will do the trick:<pre>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</pre>

_________________<MARQUEE>***************** EXCEL/VB NEWBIES ARE MY <font color="red"> FAVORITE</font>! ****************</MARQUEE>
This message was edited by phantom1975 on 2003-02-17 22:21

 

[Yogi Anand]

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.

 

[Aladin Akyurek]

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"})

 

[crsouser]

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

 

[Richard Schollar]

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.

 

[crsouser]

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

=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

 

[pgc01]

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

Hi Christopher

You did not answer Richard's question.

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.

 

[crsouser]

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

 

[pgc01]

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.

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.