Formula Help needed?

[Danny Boy]

I'm trying to modify the formula below (I have written the formula in cell
AA4) a bit, but am having trouble. The formula works as it is currently,
however, I want to add one more piece that I can't seem to get.

I'd like the NO flag (in cell AA4) to appear if cell X4 AND cell Y4 are
blank. If cell X4 and cell Y4 have dates in them, and today is greater than
the date in cell Y4, than I want the flag in cell AA4 to say YES.

Any attempt to add in the formula infomation regarding cell X4 seems to
result in errors, or FALSE outcomes. Below is the formula as I originally
wrote in (without the needed addition of the cell X4 information described
above):

=IF(Y4="","NO",IF(TODAY()>EDATE(Y4,0),"Yes",""))

Thanks much,

Dan

 

[barry houdini]

Hello Dan, I don't think you need EDATE, using EDATE(Y4,0) is just th
same as using Y4. Try this version

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

 

[Danny Boy]

Hi Barry!

Thanks for the suggestion about EDATE. I can be numb sometime (I'm still an
Excel newbie). I tried your formula, but it did not work. It merely gave me
the result (in cell AA4) of TRUE, as opposed to identifying YES (class has
been completed), or NO (class has not been completed).

Again, if a date is entered into both cells X4 & Y4, and TODAY is greater
than the date in cell Y4, the flag in cell AA4 should say YES (indicating
that the class has been completed). If however, a student doesn't complete
their class, I remove the dates from cells X4 and Y4, and then the NO flag
should appear in cell AA4 (indicating that the class has not been completed).

I also forgot to mention that cell AA4 should remain "blank" if either:

1) Dates appear in cell X4 AND Y4, but the date in cell Y4 has not yet
passed (e.g. today is not greater than the date in cell Y4).

2) If cell Z4 is blank (I forgot to mention that in my original formula). I
use cell Z4 to indicate the class section (Saturday, Sunday).

Again, thank you!

Dan

 

[Danny Boy]

I did get your original formula to work Barry (I jumped the gun on that).
However, the NO flag should only appear if cell X4 AND cell Y4 are both
blank. In your formula, the NO flag apepars if cell X4 OR cell Y4 is blank.
And again, I need to incorporate the cell Z4 information (as mentioned
previously) so that cell AA4 remains blank in the absence of a class section
being identified in cell Z4, or if today is not greater than the date in cell
Y4.

Once more thanks!

 

[David Biddulph]

Barry's formula of =IF(COUNT(X4,Y4)=2,IF(TODAY()>Y4,"Yes",""),"No") cannot
return TRUE.

Perhaps you tried to type in the formula and did so incorrectly?
Don't try to retype; use copy (from the newsgroup) and paste (into your
spreadsheet formula).

 

[Danny Boy]

See my last posting David, as I did mention that I got Barry's formula to
work, however, what I was looking for was for the "NO" flag to trigger when
BOTH X4 & Y4 are blank. Barry's formula triggers if EITHER X4 or Y4 is blank.

I also mentioned that I needed formula which would have cell AA4 remain
"blank" if either of the following conditions occur:

1) Dates appear in cell X4 AND Y4, but the date in cell Y4 has not yet
passed (e.g. today is not greater than the date in cell Y4).

2) If cell Z4 is blank

I use cell Z4 to indicate the class section (Saturday, Sunday). If a client
hasn't been assigned to a class section, and NO dates are posted in X4 and
Y4, than cell AA4 should also remain blank. If a client has been assigned to
a class I would leave cell Z4 blank. If a client has been assigned to a
class, and failed to attend, I would remove the dates in X4 and Y4, but leave
the class section information intact. Thus, cell AA4 (in this scenario) would
flag "NO', to indicate the class hasn't been completed by the student.

Again, thank you anyone for feedback. I very much appreciate it.

Dan