I am building a Gantt chart and I want to be able to say if the task is Complete, Late or Future based on the end date of the task and the % of the work complete.

I need to add something to my formula to make it say if it is a future date and less than 100% then ‘future’, however if it is a future date and 100% then ‘complete’

Ta

Nested IFs is the simple way

=IF(A1>TODAY(),IF(B1<1,”future”,”complete”),IF(B1<1,”late”,”complete”))

where A1 is the completed date and B1 is the percentage (assuming 1 = 100%)

You will need to look at exactly what you want and adjust

Also note I can’t see your picture, blocked at work

Thanks rubber that appears to have done it!

I was thinking too hard about it, making it more complicated than needs be

Being slightly nerdy you can remove one of the nested IFs (which is always good). You can see that as both the FALSE “values” in the second two IFs are “Complete”. So instead go for:
IF(B1>=100%,”Complete”,if(A1>TODAY(),”Future”,”Late”))

Can I ask how you get the part green fill on the percentage cells?
I have been looking for a way to do this for a long time!

Never used the functionality myself, but I think Data Bars under Conditional Formatting is what you need.

Conditional formatting can do that although I notice the 50 and 60 percentage cells don’t have the % in green whereas the others do which is a bit odd

Jon Peltiers web site is a good resource if you’d like to use graphs instead

Twelveski – YHM

I lifted it from another Excel sheet from a colleague at work. I use Excel 2010, and I couldn’t see it as a distinct icon set in conditional formatting, however when I copied that cell in to my spreadsheet it was fully useable in conditional formatting