# 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

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"))MIKEhttp://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 20031st – Select the cell in which your formula is located, in your case C2On the Menu Bar:2nd - Format3rd - Conditional Formatting4th – Change “Cell Value is” to “Formula Is”5th – Enter the formula:=IF(C2-B2>=7,TRUE,FALSE)Sub Menu Format:6th - Patterns7th - Choose a pretty color8th - Press OKMIKEhttp://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)MIKEhttp://www.skeptic.com/

Report •

#5
June 8, 2011 at 03:53:25
 or just...=TODAY()-7<=\$B2Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#6
June 8, 2011 at 07:39:49
 Old habits are tough to break.......MIKEhttp://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 A1Thanks

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 weekand over a week itDealing with Date terminology can be tricky.MIKEhttp://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