You are here:

Advertisement

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!

Katie

ANSWER: Katie,

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. )

--

Regards,

Tom Ogilvy

---------- 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!

Katie

Katie,

Could you send a sample file to twogilvy@msn.com

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.

--

Regards,

Tom Ogilvy

- Add to this Answer
- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 10 | Politeness = 10 |

Comment | Thank you so much!! You helped me immensely! Very fast responses too, I really appreciate all your help! |

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.