# 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..
###### Who is Participating?

=IF(AND(CELL("Format",G2)="D1",H2=""),"Complete",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)))))

Sorry Spelling is wrong in formula this is revised one.
0

=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)))
0

Author Commented:
You superstar. ..itjockey. ..

Appreciated

Just double checking
0

Author Commented:
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
0

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
0

=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)))))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.