Excel formula help

BYTEr · 24 Feb 2016 at 16:33

Hi all,

Wondering if someone could help, I've got an excel worksheet relating to an action time list, I have a column(F) with a completion target date, another column(G) with a revised completion date and a column(J) with days overdue.

I have managed to use a IF statement where in the days overdue column it will state the amount of days overdue from the completion target date (column F), however I need to combine it with the revised completion date (column G) so that if their is a revised completion date it uses this date instead of the completion target date (column F).

My current formula is:

=IF(DAYS360(F2,TODAY(),TRUE)>0,DAYS360(F2,TODAY(),TRUE)&"Days","")

Which works fine (except it returns a value of 41814Days if column F is blank).

Does anyone know what I need to add to the formula to use column G's date instead only if one has been entered.

Thanks in advance.

BYTEr · 24 Feb 2016 at 20:05

Thanks for the response guys.

The formula:

=IF(ISBLANK(F2),"",IF(ISBLANK(G2),IF(DAYS360(F2,TO DAY(),TRUE)>0,DAYS360(F2,TODAY(), TRUE)&"Days",""),IF(DAYS360(G2,TODAY(),TRUE)>0,DAY S360(G2,TODAY(), TRUE)&"Days","")))

Seems to work perfectly at the moment, much appreciated

BYTEr · 24 Feb 2016 at 21:15

Does anyone know how to conditionally format a row based on the value of 1 cell?

Eg; If column J (Days overdue) has any value then that row (A-K) is highlighted red, and if column J does not have a value it is highlighted green.

Thanks again for any help.

BYTEr · 25 Feb 2016 at 19:22

Hi guys, asking for some more help!

Forget the above, I've decided to just conditionally format the column only.

My only issue now is that when I put in a completion date (column I) the number of days overdue is still present, what do I need to do so that when a completion date is entered the overdue days cell (column J) goes blank.

Thanks in advance.

BYTEr · 25 Feb 2016 at 21:26

Thanks for the reply Confused Stu, really helpful. It's the first time I've used excel past simple stuff like finding the averages, sums etc, I only used conditional formatting for the first time a few days ago, discovered VLOOKUP today as well which is going to be really helpful in the future.

After googling however, I did initially try adding =IF(ISBLANK(I2),"", in front of the existing formula but I'm getting the error of 'You've entered too many arguments for this function' and then highlights the "" part of =IF(ISBLANK(I2),"",. I've tried copying the whole formula you've produced and I'm getting the same, I can't quite work out why as its used elsewhere in the formula.

Any help would be greatly appreciated, thanks.

BYTEr · 26 Feb 2016 at 12:53

Thanks! Works perfectly

Although after using the spread sheet I've realised I need something else

Would it be possible to produce a -Days figure in the overdue column too if something was completed before the completion target date/revised completion target date. I was just thinking if that's possible I could do a sum at the bottom of the column which would make a good performance indicator.

BYTEr · 26 Feb 2016 at 14:45

Think I might have worked out a method for the above, by creating extra columns (which I'll hide) and simply use Date Completed/Completion target date or Revised completion date which should give me a minus number if completed before target date allowing me to add these up and come up with a figure of how many days overdue through all the actions or how many days we've gained.