Action plan - Producing minus days figure if action completed before target date.

Hi all,

I was just wondering if anyone could help me as I’m currently putting together an action plan and was hoping someone could advise or offer suggestions on a few issues I’m having.

For reference, I have in column F ‘completion target date’, column G ‘Revised completion date’, column I ‘Date completed’ and in J ‘Days overdue’.

After many hours and much frustration, I have come up with a formula in column J where it automatically states the amount of days overdue if the completion target date has passed (if column G is blank), or if the revised completed date has been surpassed if one has been entered, also if a completion target date has been entered column J goes blank.

Formula:

Please Login or Register  to view this content.

This seems to work perfectly.

However, I have since thought that I could use the days overdue column as a good performance indicator by adding the sum of all the actions, but for this to work I would need a minus figure if the date completed column was before the target completion date or revised target completion date, however I’ve struggled to add this to the formula. The only way around it that I have thought of so far is to add another column directly taking date completed from the target completion date or revised completion date, I could then hide these columns and use a cell at the bottom with the performance of +/- days.

Any ideas or suggestions would be much appreciated.

Thanks in advance.

I think the formula above needs revising: if you enter "Date Completed" then "Days Overdue" is blank i.e, no test for Overdue days.

e.g. Completion Target Date (F2) 20/02/2016 , Completion Date (I2) 28/02/2016, Overdue (J2) should be 8 (??)but is blank

Date completed subtracted from your Completion date(s) will just return a +/- value so not sure why you need extra column

Try

=IF(ISNUMBER(I2),IF(ISNUMBER(G2),I2-G2,I2-F2),IF(ISNUMBER(G2),IF(G2-TODAY(),G2-TODAY(),""),IF(ISNUMBER(F2),IF(F2-TODAY(),F2-TODAY(),""),"")))