route217
asked on
tricky if formula
Hi Expert's excel 2007
I need to amend current formula to add the following condition...additional criteria
=IF(A2="",IF(C2="","",IF(A ND(G2<>"", G2<TODAY() ,H2=""),"C omplete"," Progress") ),VLOOKUP( A2,Sheet2! $A$2:$B$20 ,2,FALSE))
If g2 and h2 both have no dates then c2..
I need to amend current formula to add the following condition...additional criteria
=IF(A2="",IF(C2="","",IF(A
If g2 and h2 both have no dates then c2..
=IF(AND(CELL("Format",G2)= "D1",CELL( "Format",H 2)="D1"),C 2,IF(A2="" ,IF(C2="", "",IF(AND( G2<>"",G2< TODAY(),H2 =""),"Comp lete","Pro gress")),V LOOKUP(A2, Sheet2!$A$ 2:$B$20,2, FALSE)))
ASKER
You superstar. ..itjockey. ..
Appreciated
Just double checking
Appreciated
Just double checking
ASKER
Ok...slightly problem...when g2 has a date and h2 us blank in addition to the above then return complete. ..if g2 is blank and h2 gas date then in progress
Just to inform you that =CELL("Format",G2) return to "D1" if G2 date format is "d-mmm-yy or dd-mmm-yy". if there is some different format then modify formula as per format. below is list of Returning value & its format.
"D4" - m/d/yy or m/d/yy h:mm or mm/dd/yy
"D1" - d-mmm-yy or dd-mmm-yy
"D2" - d-mmm or dd-mmm
"D3" - mmm-yy
"D5" - mm/dd
"D6" - h:mm:ss AM/PM
"D7" - h:mm AM/PM
"D8" - h:mm:ss
"D9" - h:mm
Thanks
"D4" - m/d/yy or m/d/yy h:mm or mm/dd/yy
"D1" - d-mmm-yy or dd-mmm-yy
"D2" - d-mmm or dd-mmm
"D3" - mmm-yy
"D5" - mm/dd
"D6" - h:mm:ss AM/PM
"D7" - h:mm AM/PM
"D8" - h:mm:ss
"D9" - h:mm
Thanks
=IF(AND(CELL("Format",G2)= "D1",H2="" ),"Complet ",IF(AND(C ELL("Forma t",H2)="D1 ",G2="")," Progress", IF(AND(CEL L("Format" ,G2)="D1", CELL("Form at",H2)="D 1"),C2,IF( A2="",IF(C 2="","",IF (AND(G2<>" ",G2<TODAY (),H2=""), "Complete" ,"Progress ")),VLOOKU P(A2,Sheet 2!$A$2:$B$ 20,2,FALSE )))))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.