Nested If - driving me crazy! Please Help!

[wethernt]

Can't figure out what I am doing wrong for the life of me... Yes, it long... trying to bucket dates into 8 different buckets as follows: 2013, 2014, >120days&YR15, 91-120, 61-90, 31-60, 1-30, Current Thanks for any help.

=IF([Invoice Due Date]<=DATEVALUE(12/31/2013),"2013",
IF([Invoice Due Date]<=DATEVALUE(12/31/2014),"2014",
IF(AND([Invoice Due Date]<=DATEVALUE(12/31/2015), [Invoice Due Date]<(TODAY()-120)),"120+YR15"),
IF(AND([Invoice Due Date]>=(TODAY()-119), [Invoice Due Date]<(TODAY()-90)),"91-120"),
IF(AND([Invoice Due Date]>=(TODAY()-90), [Invoice Due Date]<(TODAY()-60)),"61-90"),
IF(AND([Invoice Due Date]>=(TODAY()-60), [Invoice Due Date]<(TODAY()-30)),"31-60"),
IF(AND([Invoice Due Date]>=(TODAY()-30), [Invoice Due Date]<(TODAY()-1),"1-30")),
"Current"))

 

[Andrew Poulsom]

Welcome to MrExcel.

This is syntactically correct, if that's what you want:

=IF([Invoice Due Date]<=DATEVALUE(12/31/2013),"2013",
IF([Invoice Due Date]<=DATEVALUE(12/31/2014),"2014",
IF(AND([Invoice Due Date]<=DATEVALUE(12/31/2015), [Invoice Due Date]<(TODAY()-120)),"120+YR15",
IF(AND([Invoice Due Date]>=(TODAY()-119), [Invoice Due Date]<(TODAY()-90)),"91-120",
IF(AND([Invoice Due Date]>=(TODAY()-90), [Invoice Due Date]<(TODAY()-60)),"61-90",
IF(AND([Invoice Due Date]>=(TODAY()-60), [Invoice Due Date]<(TODAY()-30)),"31-60",
IF(AND([Invoice Due Date]>=(TODAY()-30), [Invoice Due Date]<(TODAY()-1)),"1-30",
"Current")))))))

 

[shg]

Another ways, perhaps:

=LOOKUP(DueDate,
CHOOSE({1,2,3,4,5,6,7,8}, --"1/1/13", --"1/1/14", --"1/1/15", TODAY()-120, TODAY()-90, TODAY()-60, TODAY()-30, TODAY()),
{2013,2014,"121+","91-120","61-90","31-60","1-30","Current"})

 

[wethernt]

Welcome to MrExcel.

This is syntactically correct, if that's what you want:

=IF([Invoice Due Date]<=DATEVALUE(12/31/2013),"2013",
IF([Invoice Due Date]<=DATEVALUE(12/31/2014),"2014",
IF(AND([Invoice Due Date]<=DATEVALUE(12/31/2015), [Invoice Due Date]<(TODAY()-120)),"120+YR15",
IF(AND([Invoice Due Date]>=(TODAY()-119), [Invoice Due Date]<(TODAY()-90)),"91-120",
IF(AND([Invoice Due Date]>=(TODAY()-90), [Invoice Due Date]<(TODAY()-60)),"61-90",
IF(AND([Invoice Due Date]>=(TODAY()-60), [Invoice Due Date]<(TODAY()-30)),"31-60",
IF(AND([Invoice Due Date]>=(TODAY()-30), [Invoice Due Date]<(TODAY()-1)),"1-30",
"Current")))))))

Thanks for your assistance. Actually, new member forum error. I will move this to the Power BI forum. I am tyring to use the formula above in a powerpivot calculated column. Should work but still get type mismatch error. Thanks.