Excel formula help

BYTEr

BYTEr

BYTEr

Associate
Joined
2 Oct 2004
Posts
1,048
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.
 
For your formula to read from column G instead of F if G has an entry in it, this will do it:

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

There might well be a neater way to do it, but I'll leave that to brains bigger than mine! Also, if you wanted to fix the issue with 41814 days showing if F is blank, use this one instead:

=IF(ISBLANK(F2),"",IF(ISBLANK(G2),IF(DAYS360(F2,TODAY(),TRUE)>0,DAYS360(F2,TODAY(), TRUE)&"Days",""),IF(DAYS360(G2,TODAY(),TRUE)>0,DAYS360(G2,TODAY(), TRUE)&"Days","")))
 
It might be simpler to work with an additional column, which you hide. Call it column K. In K you put

=IF(ISBLANK(F2),IF (ISBLANK(G2), @NA, G2), MAX (F2,G2))

which should return G2 if F2 is blank, F2 if G2 is blank, the later of G2 and F2 if both are filled, and N/A if both are unfilled. Possibly I've got the True/False responses the wrong way around.

Then work column J off that.
 
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 :D
 
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.
 
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.
 
You want your Excel formula to firstly check if something is in Column I, and to leave the cell blank if it is, then run your existing formula if not. So simply, tack exactly that onto the front. :) The trick with Excel formulae is to work out very, very simply what you want to do in the smallest steps possible, then construct the formula from these little bits.

So, for the bit you want to add, it's adding =IF(ISBLANK(I2),"", onto the front, and an extra bracket at the end of the formula to account for the extra IF statement you've added around the whole formula.

As you're typing, Excel will help by showing you what it's expecting - so you when you type in =IF( Excel will then prompt it's currently looking for the question, then after you put the comma it will move the prompt to what to do if the question is true, then finally another comma will prompt for what to do if the question if false.

The actual, full formula you want is:

=IF(ISBLANK(I20,"",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",""))))

But I really would suggest spending a few minutes typing it in and seeing how it's built up as that way you'll easily be able to construct this kind of thing again when you next need it. :)
 
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.
 
That's what happens when I try to type something in a hurry on a device I don't have Excel on - I make mistakes! Sorry BYTEr. :(

We're adding "if the completion date is blank, do the formula, if not leave this cell blank" - but then the bit I've said to add at the start of the formula itself does it the other way round - if completion is blank, then leave this cell blank, if not do the formula. Plus, I've then made a typing error in the full formula.

Overall, I don't think I helped in that post! :eek:

OK, so let's add to our existing formula properly this time.

On the front, we're going to add
=IF(ISBLANK(I2),
then the original formula
then the "leave this cell blank if the completion date is filled in" onto the end
,"")

So, the full formula should be:
Code: 
=IF(ISBLANK(I2),IF(ISBLANK(F2),"",IF(ISBLANK(G2),IF(DAYS360(F2,TODAY(),TRUE)>0,DAYS360(F2,TODAY(),TRUE)&"Days",""),IF(DAYS360(G2,TODAY(),TRUE)>0,DAYS360(G2,TODAY(), TRUE)&"Days",""))),"")

Hopefully that should get rid of the error message you're seeing as, as Reaper said, we shouldn't have hit any limits of any non-prehistoric Excel versions.
 
Thanks! Works perfectly :D

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

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.
 
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.
 
You must log in or register to reply here.
Back
Top Bottom