Replace the formula in C1 with this:
=IF(B1="",IF(NOW()A1,"LATE","Due"),"Done")
or if you want to force entry of "IN" (or "in" or "In") into B1 and not
accept anything else like an 'x' or 'OK", do it this way:
=IF(B1<"IN",IF(NOW()A1,"LATE","Due"),"Done")

"Karl" wrote:

One more question


If I wanted C1 to display "LATE" if the deadline date in A1 had passed, but
not to display anything (or to display "completed") if the user had written
"IN" in B1, would that be possible?

Thanks

Karl

"JLatham" wrote:

Two steps required to do this. In the third cell (C1 and assuming 2nd cell
is B1) put this formula:
=IF(B1="","LATE","Completed")
or substitute another phrase for "Completed" if you like, or even nothing by
using ,"") as that last parameter.

Then select C1 and use Format | Conditional Formatting and set the condition
to:
Cell Value Is | equal to
and type the word LATE into the 3rd area on that row. Then click the
Format... button and choose red font color along with Bold. [OK] to close
the format dialog, [OK] again to close the Conditional Formatting dialog.
Should work as you desire.


"Karl" wrote:

Hi,

I want to design a spreadsheet which tracks when certain pieces of work are
due and whether theyve arrived or not.

At the moment there are three cells, all in a row that I want to interact
with each other.

In the first cell the user will enter the date on which the work is due. In
the second cell, I want the user to enter €˜in, when the work is in. If no
text is entered is entered into the second cell, I want the third cell to
display the word €˜LATE, in red caps.

Ive seen something like this done, but I dont know how to do it myself.
Can anyone give me an advice or point me to a good tutorial?

Thanks

Karl