Making too much of a formula

[Mick]

I started off with a simple formula but am I making too much of it?

I wanted a cell (Y38) to give the status of the record based on the date in
cell W38.
If there was no date in X38 (date a letter was sent) then I would either get
an 'overdue' message or a 'complete' message depending on the date in W38

W= date due
X = date sent
Y = status

=IF(TODAY()<=W38,"",IF(AND(TODAY()>W38,X38=""),"Overdue","Complete"))

This worked fine, but I noticed if the letter was sent early (add a date to
cell X38) the status cell would remain blank and today's date had passed.

So I thought the following might work.
=IF(X38<>"","Complete",IF(TODAY()<=W38,"",IF(AND(TODAY()>W38,X38=""),"Overdue","Complete")))

It does work, but Excel is telling me the formula is inconsistent, does this
matter, am I over complicating things?

Appreciate any feedback.
Thanks
Mick

 

[MartinW]

Hi Mick

It does work, but Excel is telling me the formula is inconsistent, does this
matter, am I over complicating things?

No, Excel is just trying to be helpful by telling you that your formula is
inconsistent
with the formulas in the adjacent cells. Turn off error checking and no more
(helpful??) messages will appear!

HTH
Martin

 

[David Biddulph]

Yes, you are overcomplicating things in your second formula. You don't need
the final IF test, as the AND() conditions you quote have to be true to get
there in the first place.
Won't
=IF(X38<>"","Complete",IF(TODAY()<=W38,"","Overdue"))
give the same result?
If in doubt, draw up a truth table.

 

[Mick]

David
Many thanks for the 'simple' version.

Martin
Thanks also for your response.
Mick