Microsoft Excel - excel pro - Asked By anu anu on 22-Oct-13 09:00 AM

i need one excel help, i have some data in column A And B . like

case open date  and  case close date , now i want if case colse date is blank and case close date is gateter than toadys date in next column will be highlight by Followup. other wise if i case cose date enter shows done

Hello Anu,

=IF(OR(B2="";B2>TODAY()),"followup","done")

Something like this?

Regards

Harry

 Hi harry,
 
 it`s fine . but i need like
 1* if Case open date pending since last 2 days then shows follow up .
 2* if  case close date filled then shows Done .
 3 if case open date not less then 2 days then shows Pending.

regards
Usha
 

Hello Anu,
Possibly something like this.

=IF(A2>TODAY()-2,"pending",IF(OR(A2<TODAY()-2,B2="",B2>TODAY()),"followup","done"))

Regards

Harry

Hello Anu,
Possibly something like this.

=IF(A2>TODAY()-2,"pending",IF(OR(A2<TODAY()-2,B2="",B2>TODAY()),"followup","done"))

Regards

Harry

Hello Anu,
Possibly something like this.

=IF(A2>TODAY()-2,"pending",IF(OR(A2<TODAY()-2,B2="",B2>TODAY()),"followup","done"))

Regards

Harry

hi harry.

 i need like this .

 

Case Open Date	Case closed date	Satuts
10/19/2013	10/23/2013	Close
10/23/2013		Pending
10/19/2013		Followup

 if My Case open date > 2 days show followup
 if it`s not shows Pending
 and if case closed date filled shows closed.

thanks 
anu

 

 Hi Harry,


if My Case open date > 2 days show followup
 if it`s not shows Pending
 and if case closed date filled shows closed.

Case Open Date	Case closed date	Satuts
10/23/2013	10/23/2013	Clsoed
10/23/2013		Pending
10/19/2013		Followup

Regards
ANu

Hello Anu,

=IF(AND(A2<>B2,A2>TODAY()-2),"pending",IF(B2="","followup",IF(OR(A2=B2,B2>=TODAY()),"done")))

Try this.
Harry

thank you harry it`s working

regards
Usha

Hi harry,

 it`s working fine but one thing if i put colsing date garter then opening date its show Pending .but i need when i put closing date its shows Closed.

regards
Usha

Hi Usha,

An example would make it clearer.

Harry

Hi Usha,

=IF(B2<>"","done",IF(AND(A2<>B2,A2>TODAY()-2),"pending",IF(B2="","followup")))

Maybe this.

Harry

thanks Harry . it`s working perfectly fine

 hi  harry ,

 i put your formula in column C it`s workinf fine , now i have one requirement

now i need like if 1st satuts is Follow up then in next DAte of 1st reminder should be case open date +2days and Satuts of 1st Reminder if case open day last 4 days and yet not closed shows Need Followup ,if Closed date filled then closed and other wise Pending

Case Open Date	Case closed date	Satuts	Date of 1st Reminder	Satatus of 1st Reminder
10/23/2013		pending
10/19/2013		followup	10/21/2013	Need Followup
10/19/2013	10/24/2013	done	10/21/2013	Done
10/23/2013	10/23/2013	done
10/21/2013		followup	10/23/2013	Pending

thanks
Anu

Hello Usha,
I have been travelling.
Perhaps this will do what you want to some degree.
In D2:  =IF(C2="followup",A2+2,"")
In E2:  =IF(D5<>"",IF(B5<>"","done",IF(TODAY()-A5>4,"Needs follow up","pending")),"")
This will not leave the detail of the date of first reminder as the formula relies on the wording in C2 which is dyanmic.  If you want to keep that detail you would have to write a little macro to convert the formula to a value (or do it manually) and if you are doing it by macro possibly the whole thing could be done in the macro.
Regards
Harry

PS:  Try this in D2:  =IF(OR(C4="followup",B4-A4>2),A4+2,"")
Harry