Is there an "ISDATE" type function in Excel

ru4ward

New Member
Joined
Jan 23, 2003
Messages
4
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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
 
Upvote 0
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.
 
Upvote 0
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"})
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top