# Thread: How do I check 4 a date?

1. 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

2. 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:

3. 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.

4. I'll await the next time you ask this question, in 2011.

5. 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. 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.

7. [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. [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. 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]

10. [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. 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!

12. [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. Agreed.

TX

14. [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!

#### Posting Permissions

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