Matt Pinkston
asked on
Help with Excel formula
I have attached a xls to help with this question
Columns
Status
Date Entered
Date Complete
Date Due
Todays Date
Days (Column for formula)
Overdue (column for secondary formula)
Logic for formula
If Status NE Complete AND Status NE Cancelled Then
Days = Todays Date - Date Entered
Else
Days = Date Complete - Date Entered
Second
If Status NE Complete AND Status NE Cancelled AND Todays Date > Date Due then Column = Yes
DateCheck.xlsx
Columns
Status
Date Entered
Date Complete
Date Due
Todays Date
Days (Column for formula)
Overdue (column for secondary formula)
Logic for formula
If Status NE Complete AND Status NE Cancelled Then
Days = Todays Date - Date Entered
Else
Days = Date Complete - Date Entered
Second
If Status NE Complete AND Status NE Cancelled AND Todays Date > Date Due then Column = Yes
DateCheck.xlsx
F2
=MAX(IF(AND(A2<>"Complete",A2<>"Cancelled"),E2-B2,C2-B2),0)
G2=IF(AND(A2<>"Complete",A2<>"Cancelled",E2>D2),"Yes","No")
Personally I would have done away with column E and replaced if with Today() directly in formulas.
Please try this:
F2 =IF(OR(A2="Complete",A2="Cancelled"),E2-B2,IF(C2="","",C2-B2))
G2 =IF(D2="","",IF(OR(A2="Complete",A2="Cancelled",E2>D2),"Yes",""))
Because in Column Date Due, some rows are blank.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent
G2 =IF(F2<>"",IF(E2>D2,"Yes",