How would I edit this formula to do what I want?

[Danny Boy]

I'd like to modify this formula so that the NO Flag appears if a date is
missing in BOTH X4 and Y4. The current formula generates the NO flag, if a
date is missing in either X4 or Y4. Any suggestions?

=IF(COUNT(X4,Y4)=2,IF(TODAY()>Y4,"Yes",""),"No")

 

[Rick Rothstein]

As far as I can see, the formula you posted appears to already do what you
are asking for. Perhaps if you describe what you want for each possible X4,
Y4 combination, that might helps us to be able to answer your question.

 

[Chris]

Try this:

=IF(OR(ISBLANK(X4),ISBLANK(Y4)),"",IF(COUNT(X4,Y4)=2,IF(TODAY()>Y4,"Yes",""),"No") )

 

[Danny Boy]

That didn't seem to work Chris. The YES flag triggered per the formula below,
but not the NO flag (even if X4 and Y4 were blank).

What I am trying to do, is to have a formula that will trigger a "YES" flag
(in cell AA4) if today's date is greater than the date posted in cell Y4 (the
ending date of an identified class). Cells X4 is the start date of a class,
and cell Y4 is the end date.

If a student no shows for that class, I would remove both the start date
(X4) and the end date (Y4). Once BOTH the start and end dates are removed,
than the flag in AA4 should say "No" (signifying that a student did not
complete class).

If dates are present in cells X4 and Y4, but the date in cell Y4 has not yet
passed (the scheduled class has either not yet begun, or is in process), than
cell AA4 should remain blank.

Thanks for any help. Rick I hope that clarifies what I'm looking to do.

Dan

 

[Rick Rothstein]

ISBLANK will not work if the OP's dates and/or his "empty cells" are the
result of a formula. For example, on an empty worksheet, put this formula in
A1...

=IF(A2="","","A2 is not empty")

and put this formula in B1...

=ISBLANK(A1)

it displays FALSE because the cell is not blank... it has a formula in it.

 

[Rick Rothstein]

Sorry, but I am still confused... the formula you posted in your original
message seems to do exactly what you are describing here.

 

[Danny Boy]

Actually, if I remove only one of the two dates (from my current formula)
from X4 or Y4, the "No" flag appears. I only want the "No" flag to appear
when BOTH dates X4 (start date) and Y4 (end date) are removed leaving these
cells blank.

 

[T. Valko]

=IF(COUNT(X4,Y4)=2,IF(TODAY()>Y4,"Yes",""),"No")

The COUNT function is testing to see if there are 2 dates entered in the
cells. If COUNT condition is TRUE then the formula processes this:

IF(TODAY()>Y4,"Yes","")

If the COUNT condition is FALSE then the formula returns "No".

So, the formula *is* doing what you say you want in your explanation. You've
also mentioned that if either cell X4 or Y4 is empty the formula returns
"No" which apparently you don't want but you haven't said what result you do
want when that condition is present. If either X4 or Y4 does not contain a
date what result do you want?

Let's try this:

Assuming today's date is 8/1/2009...

................X...............Y
........1/1/2009.....1/20/2009
........1/1/2009.....9/1/2009
........1/1/2009....................
...........................9/1/2009
.........................................

The current formula returns these results:

Yes
<blank>
No
No
No

What result do you want for each of those examples?

 

[Chris]

Try this


=IF(AND(ISBLANK(X4),ISBLANK(Y4)),"No",IF(AND(ISBLANK(X4)=FALSE,ISBLANK(Y4)),"",IF(TODAY()>Y4,"yes","")))