QUESTION: Hi Tom,
In cells V:V I have the formula, =IF(T25>U25,"Closed on Time","Overdue"). Column V:V is 'Status', Column 'T' s Fix Due Date and Column U is 'Close Date'... right now if the Close Date is later than the Due Date then 'Overdue' shows up. If the close date is on or before the due date 'Closed on Time' shows up.
Column V:V says "Overdue" right now even when there isn't any values in columns T or V... I need it so that if there isn't any values in a Close Date cell nothing will show up under the corresponding Status cell. I've been playing around with the IF ISBLANK function and I can't seem to get it right. Could you help me out with this? I really appreciate it!
I assume you have Excel 2007 or later. I further assume you want to suppress any error values in column V.
>I need it so that if there isn't any values in a Close Date cell nothing will show up
So the close date cell appears to be in column U. Then I would do:
V25: =IFERROR(IF(LEN(TRIM(U25))=0,"",IF(T25>U25,"Closed on Time","Overdue")),"")
If you want to test if either T25 or U25 or Both are blank, then show nothing then
V25: =IFERROR(IF(OR(LEN(TRIM(T25))=0,LEN(TRIM(U25))=0),"",IF(T25>U25,"Closed on Time","Overdue")),"")
I use Len(trim())=0 because that would work if there were say an IF statement in one of the cells that produces a null string "" or a " " or the cell has a space where isblank would not work. (some people clear cells by using the space bar which puts a space in the cell although it looks blank. )
---------- FOLLOW-UP ----------
QUESTION: Perfect! That is exactly what I needed. Thank you so much!
Now for a more challenging question...
I am trying to make a template that will calculate the FRT (Problem Report Fix Response Time)for the given report period. For this I need to calculate the Major problem reports Closed on time, Minor Due, Minor Closed on Time and Minor Due. I need some way to count each of these from the data I have on another sheet for the specified report month.
There are 5 columns in my data sheet that will be relevant to my calculations. The first is A:A 'Date Reported', the second L:L 'TL Severity', the third, T:T 'Fix Due Date', the fourth U:U 'Close Date'and the fifth, V:V 'Status'.
For Major and Minor Due I need to count all the problem reports that are due for that month... so for example, if I am reporting for the month of February 2012 and looking at Minor Due I would need to look at T:T for 'Fix Due Date' and look for the month of february and count all the reports that were due for that month that were considered Minor.
For Major and Minor Closed on Time I would need to look at V:V 'Status' and see which ones were Closed on Time for the given report month and count them each (minors and majors seperately).
These are going to be 4 seperate formulas- i've attached a screenshot of the formula sheet where i need to insert the formulas for Major Closed on Time, Minor Closed on Time, Minor Due and Major Due. Also, I've attached a screenshot of my data sheet that im pulling all of this data from.(everything relevant to this question is highlighted in green)
The challenge here also is to figure out a way to connect all of this to the report period that is at the top of my formula sheet (C:4).... So if they are reporting for the month of june '1/30/13' I would need these formulas connected to that date so it can grab all the data for that month.
Essentially what I want is to make it as simple as possible for the reporting... I want it so that people put in the date of the report month, and to have all those metrics calculated automatically for that month.
I know this is kind of a lot of information. I tried to explain it as clearly as possible, but if you have any questions let me know! I really appreciate your help Tom!
Could you send a sample file to firstname.lastname@example.org
with the above description of what you want to do?
It is much easier to work on the file than to be flipping back and forth between pictures trying to figure everything out.
If you don't want to send the sample file, then you would add conditions to this type of formula:
=COUNTIFS(A:A,">="&DATEVALUE("Dec 1, 2012"),A:A,"<="&DATEVALUE("Dec 31, 2012"),L:L,"Major")
which I previously gave you.