Link to home
Start Free TrialLog in
Avatar of route217
route217Flag for United Kingdom of Great Britain and Northern Ireland

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(AND(G2<>"",G2<TODAY(),H2=""),"Complete","Progress")),VLOOKUP(A2,Sheet2!$A$2:$B$20,2,FALSE))

If g2 and h2 both have no dates then c2..
Avatar of Naresh Patel
Naresh Patel
Flag of India image

=IF(AND(CELL("Format",G2)="D1",CELL("Format",H2)="D1"),C2,IF(A2="",IF(C2="","",IF(AND(G2<>"",G2<TODAY(),H2=""),"Complete","Progress")),VLOOKUP(A2,Sheet2!$A$2:$B$20,2,FALSE)))
Avatar of route217

ASKER

You superstar. ..itjockey. ..

Appreciated

Just double checking
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
=IF(AND(CELL("Format",G2)="D1",H2=""),"Complet",IF(AND(CELL("Format",H2)="D1",G2=""),"Progress",IF(AND(CELL("Format",G2)="D1",CELL("Format",H2)="D1"),C2,IF(A2="",IF(C2="","",IF(AND(G2<>"",G2<TODAY(),H2=""),"Complete","Progress")),VLOOKUP(A2,Sheet2!$A$2:$B$20,2,FALSE)))))
ASKER CERTIFIED SOLUTION
Avatar of Naresh Patel
Naresh Patel
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial