Show Text if a cell has ever had certain value

**jhalpin** · 02-25-2014, 03:53 PM

Hi, hoping someone can help me. I have a excel file that tracks completion rates and has lots of formulas but there is one that I cannot figure out.

I want a formula that will display "not completed in time" (for example) if completion isn't 100% by the planned completion date. The problem I have is that I want to have a record of this and if the completion rate becomes 100% after the planned completion date it will no longer show "not completed in time". Is there a way to setup a formula that will show data in a cell if the formula has EVER met certain criteria. Hope I'm making sense.

**Moo the Dog** · 02-25-2014, 05:19 PM

You could use a simple IF statement in a cell to do that. Assuming the percentage of completion value will be recorded somewhere on the sheet, you could just use:

=IF(A1<100%,"Not completed in time","")

Where A1 is the cell storing the percentage value completed. Adjust as needed.

- Moo

**jhalpin** · 02-25-2014, 05:45 PM

Hi Moo, thanks for the reply.

I don't think a simple IF statement will work. I will explain a bit more as maybe I wasn't clear.

Assume the cells have the following:
A1 = Planned completion date
B1 = Completion percentage
C1 = Completed in time (yes or no)

If A1 has today's date and 100% has been entered into cell B1 tomorrow (or sometime after) I would like C1 to say it wasn't 100% completed by the planned completion date.
Alternatively if B1 is marked @ 100% prior to the date in A1 I would like it in C1 it was completed in time.

I was thinking of using the today() formula but obviously that will change daily so probably wont work.
Is there any way to automatically put a the date when a cell B1 was @ 100%