working with dates and text

[Felicia Melocoton]

Need help with this Formula

=IF(OR(AND(E8>0, E8<$H$7, E8=""), And(E8>0, E8<$H$7, E8)),"Fail","Pass"

My Question is:

D8 column is the Date when Form was given, so there is data

E8 column is Date completed - If there is data entered which is a date, the result on F8 column which is the status column should be a Pass, automatically

E8 column is Date completed - if there is no data entered, the result on F8 column, which is the status column should be a Fail

again, please help!

 

[tactps]

How about:
=IF(E8=0,"Fail","Pass")

You can add formulas instead of "Pass" to include your other criteria.

 

[Zack Barresse]

Hi,

Excel native worksheet functions fall short (compared to VBA) when checking for an actual Excel validated date. You can test for this in VBA, but there is no built in function to do so. I usually use a UDF such as the below to add such functionality to my spreadsheets...


<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Function</SPAN> ISADATE(cel <SPAN style="color:#00007F">As</SPAN> Range) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>
<SPAN style="color:#007F00">' Zack Barresse, 2004</SPAN>
    <SPAN style="color:#00007F">If</SPAN> IsDate(cel) <SPAN style="color:#00007F">Then</SPAN> ISADATE = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>


Then just test like ...

=ISADATE(A1)

This will return a TRUE if it is a date recognized by Excel, or a FALSE if it is not. I would have used ISDATE, but that is the reserved method's name in VBA.



HTH


(Hiya Tactps!!)

 

[Felicia Melocoton]

Thank you tactps, your formula works just great.

=IF(E8=0,"Fail","Pass")

Have a great day!