Returning project status based on dates in multiple cells using a nested IF function


I am trying to create a cell that shows whether a project is on track, late or ahead of schedule using multiple dates derived from multiple tasks within the project.

For example, if the cell is filled then it will check to see if the date is equal to, greater than or less than the planned finish date to show if it is on track, late or ahead. If the cell is blank however, then I want it to check the previous task/cells to check if that is filled and if so return the correct answer. If that cell is also blank then it checks the previous cell and so on. 

I beleive that this is the best way to do this as it might be too complicated to check against the first task using the criteria and then before it offers the answer checks the next task to check it isn’t filled; if it isn’t it would keep the answer from the previous cell ,if it is filled then it applys to that cell to return either on track, ahead or late.

I have tried to use a nested IF function to complete this as shown below. I am aware that there may be too many nested IFs but even so the formula only works on the first lot of cells even if it is blank, rather than moving on to the next cell to check. Excel accepts the formula but it doesn’t work for some reason.

=IF(F36=F35, “ON TRACK”, IF(F36>F35, “LATE”, IF(F36<F35, “AHEAD”, IF(F36=””, IF(F34=F33, “ON TRACK”, IF(F34>F33, “LATE”, IF(F34<F33, “AHEAD”, IF(F34=””,IF(F32=F31, “ON TRACK”, IF(F32>F31, “LATE”, IF(F32<F31, “AHEAD”, IF(F32=””,IF(F30=F29, “ON TRACK”, IF(F30>F29, “LATE”, IF(F30<F29, “AHEAD”, IF(F30=””,IF(F28=F27, “ON TRACK”, IF(F28>F27, “LATE”, IF(F28<F27, “AHEAD”, IF(F28=””,IF(F26=F25, “ON TRACK”, IF(F26>F25, “LATE”, IF(F26<F25, “AHEAD”, IF(F26=””,IF(F24=F23, “ON TRACK”, IF(F24>F23, “LATE”, IF(F24<F23, “AHEAD”, IF(F24=””,IF(F22=F21, “ON TRACK”, IF(F22>F21, “LATE”, IF(F22<F21, “AHEAD”, IF(F22=””,IF(F20=F19, “ON TRACK”, IF(F20>F19, “LATE”, IF(F20<F19, “AHEAD”, IF(F20=””,””))))))))))))))))))))))))))))))))))))

I am also aware that using the IFS function might be useful but I am unsure of how this works.

I am using Excel 2016 and Windows.

Any help would be greatly appreciated! Smile And apologies if this has been asked before but I cannot find a solution anywhere. 

By: Callum

2 thoughts on “Returning project status based on dates in multiple cells using a nested IF function

  1. Brookwood Camps

    What i don’t realize is in reality how you are now not really a lot more smartly-preferred than you may be right now.
    You are very intelligent. You already know therefore significantly with regards to this topic, produced
    me in my view imagine it from a lot of various angles.
    Its like women and men are not involved except it’s one thing to do with Woman gaga!
    Your own stuffs great. At all times maintain it up!

Comments are closed.