Solved excel If function

June 7, 2011 at 07:52:25
Specs: Windows XP
I want to log calls coming in. If a call come in I put the date in the B column eg B2. Calls must be responded to within 7 days. In C column there is a call returned date eg C2. I want a way that highlights a cell if B2 is more than 7 days before today but only if c2 is blank.
Can you help please

See More: excel If function

Report •


✔ Best Answer
June 9, 2011 at 12:14:03
First, go back to your original set up of:

Status	Date Rec.	Date Comp.

Try this formula in cell A1:

=IF(C2<>"","",IF((B2+7)<=TODAY(),"Overdue","OK"))

MIKE

http://www.skeptic.com/



#1
June 7, 2011 at 08:32:56
Look up Conditional Formatting in the Excel help files or Google it.

Once you understand how Conditional Formatting works, you should be able to construct a formula (or "rule") that will return TRUE when the conditions you need are met.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 7, 2011 at 11:52:24
This is for Excel 2003, see how it works for you:

Conditional Formatting 2003

1st – Select the cell in which your formula is located, in your case C2

On the Menu Bar:

2nd - Format
3rd - Conditional Formatting
4th – Change “Cell Value is” to “Formula Is”
5th – Enter the formula:

=IF(C2-B2>=7,TRUE,FALSE)

Sub Menu Format:
6th - Patterns
7th - Choose a pretty color
8th - Press OK

MIKE

http://www.skeptic.com/


Report •

#3
June 7, 2011 at 12:33:34
Mike,

How will that formula address the "if B2 is more than 7 days before today but only if c2 is blank" requirement?

I'm thinking that TODAY() needs to be used someplace.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
June 8, 2011 at 03:26:19

"if B2 is more than 7 days before today but only if c2 is blank"

My eyes must be going, I never say that line, and you are correct about needing to use a =TODAY() function, this should work:

=IF((TODAY()-7)<=$B2,TRUE,FALSE)

MIKE

http://www.skeptic.com/


Report •

#5
Report •

#6
June 8, 2011 at 07:39:49
Old habits are tough to break.......

MIKE

http://www.skeptic.com/


Report •

#7
June 8, 2011 at 09:04:38
Right, these dont seem to be working.
I will re-clarify ...

So collumn A is the status column, B is date recieved, C is date completed.

If the date in B1 is within the last week the text in A1 should be "OK" if its over a week it should be "OUTSTANDING".

However if there is a date in C1 (the completion date) then there should be nothing in A1

Thanks


Report •

#8
June 8, 2011 at 09:56:52
Your first request was;

I want a way that highlights a cell if B2 is more than 7 days before today but only if c2 is blank.

The last formula I posted will do that using Conditional Formatting.

Now you want:

If the date in B1 is within the last week the text in A1 should be "OK" if its over a week it should be "OUTSTANDING".

I think I understand what you want, but could you post an example, just so I'm sure of what you mean by

is within the last week
and
over a week it

Dealing with Date terminology can be tricky.

MIKE

http://www.skeptic.com/


Report •

#9
June 9, 2011 at 02:15:06
I have worked out how to get the OK and overdue bit with =IF(A2-C2>7,"Overdue","OK") where A2 is just set to =TODAY() and C2 is the date it came in. I just need help with the conditional formating of the OK and overdue now in the B column. I want the cell in B column to go blank if the cell in column D (completed date) is filled in. eg if there is a date in D7 B7 should be blank (or white text), if there is no date in D6 then B6 should be left.

I've confused myself with this one but think I'm simplifying it now


Report •

#10
June 9, 2011 at 12:14:03
✔ Best Answer
First, go back to your original set up of:

Status	Date Rec.	Date Comp.

Try this formula in cell A1:

=IF(C2<>"","",IF((B2+7)<=TODAY(),"Overdue","OK"))

MIKE

http://www.skeptic.com/


Report •

#11
June 10, 2011 at 06:52:47
Thats the one! thanks
I even changed it to say complete is comp date was filled in

=IF(C2<>"","Complete",IF((B2+7)<=TODAY(),"Overdue","OK"))


Report •


Ask Question