Due date vs when NEW ETA is entered - Formula help

Amanda Carta

Good morning. I know I'm missing a closing bracket somewhere but I can't figure out where. What I'm trying to do is show 1 - 4 wks out and then anything further show 5+ Wks out BUT… I need it to look at the due date UNLESS a NEW ETA is entered, then I need it to look there. Here's the formula I have thus far

IF(TODAY() > [Due Date]@row , "Late", IF([Due Date]@row = TODAY(), "Today", IF(WEEKNUMBER([Due Date]@row ) = WEEKNUMBER(TODAY()), "This Week", IF(WEEKNUMBER([Due Date]@row ) = WEEKNUMBER(TODAY()) + 1, "Next Week", IF(WEEKNUMBER([Due Date]@row ) = WEEKNUMBER(TODAY()) + 2, "2 Wks Out", IF(WEEKNUMBER([Due Date]@row ) = WEEKNUMBER(TODAY()) + 3, "3 Wks Out", IF(WEEKNUMBER([Due Date]@row ) = WEEKNUMBER(TODAY()) + 4, "4 Wks Out", "5+ Wks Out", IF([NEW ETA]@row <> "", IF(TODAY() > [NEW ETA]@row , "Late", IF([NEW ETA]@row = TODAY(), "Today", IF(WEEKNUMBER([NEW ETA]@row ) = WEEKNUMBER(TODAY()), "This Week", IF(WEEKNUMBER([NEW ETA]@row ) = WEEKNUMBER(TODAY()) + 1, "Next Week", IF(WEEKNUMBER([NEW ETA]@row ) = WEEKNUMBER(TODAY()) + 2, "2 Wks Out", IF(WEEKNUMBER([NEW ETA]@row ) = WEEKNUMBER(TODAY()) + 3, "3 Wks Out", IF(WEEKNUMBER([NEW ETA]@row ) = WEEKNUMBER(TODAY()) + 4, "4 Wks Out", "5+ Wks Out"))))))))))))))))

Jgorsich

Sometimes it helps to separate out your clauses visually. Here is your function with a new paragraph after every "true" result:

IF(TODAY() > [Due Date]@row , "Late",

IF([Due Date]@row = TODAY(), "Today",

IF(WEEKNUMBER([Due Date]@row ) = WEEKNUMBER(TODAY()), "This Week",

IF(WEEKNUMBER([Due Date]@row ) = WEEKNUMBER(TODAY()) + 1, "Next Week",

IF(WEEKNUMBER([Due Date]@row ) = WEEKNUMBER(TODAY()) + 2, "2 Wks Out",

IF(WEEKNUMBER([Due Date]@row ) = WEEKNUMBER(TODAY()) + 3, "3 Wks Out",

IF(WEEKNUMBER([Due Date]@row ) = WEEKNUMBER(TODAY()) + 4, "4 Wks Out", "5+ Wks Out",

IF([NEW ETA]@row <> "", IF(TODAY() > [NEW ETA]@row , "Late",

IF([NEW ETA]@row = TODAY(), "Today",

IF(WEEKNUMBER([NEW ETA]@row ) = WEEKNUMBER(TODAY()), "This Week",

IF(WEEKNUMBER([NEW ETA]@row ) = WEEKNUMBER(TODAY()) + 1, "Next Week",

IF(WEEKNUMBER([NEW ETA]@row ) = WEEKNUMBER(TODAY()) + 2, "2 Wks Out",

IF(WEEKNUMBER([NEW ETA]@row ) = WEEKNUMBER(TODAY()) + 3, "3 Wks Out",

IF(WEEKNUMBER([NEW ETA]@row ) = WEEKNUMBER(TODAY()) + 4, "4 Wks Out", "5+ Wks Out"

))))))))))))))))

Look at the bold line - you've got both the "true" and "false" actions satisfied, and then you add a 3rd (not permitted) action where you start a new if statement. This will cause it to break.

This really looks like you accidentally pasted two if() formulas together and the function was intended to end at the bold line.

If you really want the second half evaluated also, figure out its relationship to the first half - possibly make each conditional into an and() or or() statement. I've not assessed the number of closing parens.

Edit - re-reviewing your opening question. If you want to make the evaluation dependent upon ETA, you could wrap the whole thing in an additional if() statement. It isn't elegant, but it would likely work:

if([NEW ETA]@row ="",

IF(TODAY() > [Due Date]@row , "Late",
IF([Due Date]@row = TODAY(), "Today",
IF(WEEKNUMBER([Due Date]@row ) = WEEKNUMBER(TODAY()), "This Week",
IF(WEEKNUMBER([Due Date]@row ) = WEEKNUMBER(TODAY()) + 1, "Next Week",
IF(WEEKNUMBER([Due Date]@row ) = WEEKNUMBER(TODAY()) + 2, "2 Wks Out",
IF(WEEKNUMBER([Due Date]@row ) = WEEKNUMBER(TODAY()) + 3, "3 Wks Out",
IF(WEEKNUMBER([Due Date]@row ) = WEEKNUMBER(TODAY()) + 4, "4 Wks Out", "5+ Wks Out"))))))),

IF([NEW ETA]@row <> "", IF(TODAY() > [NEW ETA]@row , "Late",
IF([NEW ETA]@row = TODAY(), "Today",
IF(WEEKNUMBER([NEW ETA]@row ) = WEEKNUMBER(TODAY()), "This Week",
IF(WEEKNUMBER([NEW ETA]@row ) = WEEKNUMBER(TODAY()) + 1, "Next Week",
IF(WEEKNUMBER([NEW ETA]@row ) = WEEKNUMBER(TODAY()) + 2, "2 Wks Out",
IF(WEEKNUMBER([NEW ETA]@row ) = WEEKNUMBER(TODAY()) + 3, "3 Wks Out",
IF(WEEKNUMBER([NEW ETA]@row ) = WEEKNUMBER(TODAY()) + 4, "4 Wks Out", "5+ Wks Out"
)))))))

)

Note that I changed the bolded line (you need to close that set of if() conditionals) and modified the number of parens at the bottom (you already closed half of them at the bolded line).