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

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,442
Office Version
  1. 365
Platform
  1. 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
54,442
Office Version
  1. 365
Platform
  1. 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
...
 

Watch MrExcel Video

Forum statistics

Threads
1,108,738
Messages
5,524,543
Members
409,583
Latest member
gkarthick

This Week's Hot Topics

Top