How to read a date format in a cell or a date

[rodebump]

It is immaterial to me whether a real date is in a cell, but that i can verify e date in the a format of 10/20/1999 is in a cell.

It would be nice if there was a function called isdate. This is where Bill wandered off into VBA. I have no interest or time to learn VBA.

I understand about the serial number behind a date format.

Therefore, is it possible to write a logic formula to check for the format? Example =if(A1=##/##/####, if_true, if_false)? Where # is a number. I do not care whether 05/59/0001 is entered as a date; i do but i am beyond being picky. I would like to verify that two numbers (including leading zero), a hash, two numbers (including leading zero), a hash and four numbers have been typed into a cell. If i did not have to worry about leading zeros that would be even better.

I use Excel 2003 and 2007. OS is Winxp.

 

[AhoyNC]

Matbe something like this:
<TABLE style="WIDTH: 124pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=165><COLGROUP><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2267" width=62><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; WIDTH: 26pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=35> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #bfbfbf; WIDTH: 51pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=68>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #bfbfbf; WIDTH: 47pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=62>B</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>10/3/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Date</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #bfbfbf; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>7/33/11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Not Date</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 338pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=451><COLGROUP><COL style="WIDTH: 338pt; mso-width-source: userset; mso-width-alt: 16493" width=451><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 338pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=451>Formula in B2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>=IFERROR(IF(DATEVALUE(TEXT(B2,"mm/dd/yyy")),"Date"),"Not Date")</TD></TR></TBODY></TABLE>

IFERROR ia a 2007 & 2010 function.

 

[Scott Huish]

That would return TRUE for 982. In which case you could just do ISNUMBER.

=IF(ISNUMBER(A1),"Date","Not a Date")

Changing a cells format does not cause Excel to recalculate, this might come closer, although you may have to hit F9 to recalculate.

=IF(AND(ISNUMBER(A1),CELL("format",A1)="D4"),"Date","Not a Date")

 

[rodebump]

Thank you for replying. I will try your suggestion(s).