Check if a valid date?

Guanjin Peter

Active Member
Joined
May 21, 2008
Messages
429
I'm currently using the below code to check if it's blank. Actually I wanted to check if it is a valid date. I can't really use:
=IF(B1=dd-mm-yyyy,TEXT(WEEKDAY(B1),"dddd"),"")
so any idea?

Code:
=IF(B1="","",TEXT(WEEKDAY(B1),"dddd"))
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,128
Office Version
365
Platform
Windows
Dates are actually stored as Numbers in Excel (specifically, the number of days since 1/1/1900). So you can use the ISNUMBER function, i.e.

=IF(ISNUMBER(B1),TEXT(WEEKDAY(B1),"dddd"),"")
 

Guanjin Peter

Active Member
Joined
May 21, 2008
Messages
429
there wasn't a need to specifcy true or false...that's strange.....

well it works wonders! Thanks!

It'll work for Vb code too?

like:
If Range("b1").Value = "ISNUMBER(B1)"
 
Last edited:

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,934
If it is a number it is presumed true and if it is not then it is presumed false.

In if statements =TRUE and =FALSE are not really needed., but you could put:

=IF(ISNUMBER(B1)=TRUE,TEXT(WEEKDAY(B1),"dddd"),"")

But it is redundant.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,128
Office Version
365
Platform
Windows
It'll work for Vb code too?

like:
If Range("b1").Value = "ISNUMBER(B1)"
Actually, VBA has an ISDATE function that works even better, as it differentiates numbers from dates (presumably by how they are formatted).

So, in VBA, you can use:
Code:
 If IsDate(Range("B1")) Then
...
 

Forum statistics

Threads
1,077,662
Messages
5,335,561
Members
399,024
Latest member
rokcel389

Some videos you may like

This Week's Hot Topics

Top