Working but gotta be a better way!

[jzibton]

Every day I send an open order report to my cust service group. To make it simpler to read and understand I make orders group orders into categories based on a ship date.
Older,
2 days late,
1 day late,
Today
Tomorrow
2 days out
Future

We only work M-F but there might be a Saturday or Sunday ship date entered so I have to factor the weekend in. I currently have a 'what if' statement for every day of the week, and just replace the statement based on what day it is.

Monday	=IF(I2=TODAY(),"TODAY",IF(I2=TODAY()-3,"1 Day Late",IF(I2=TODAY()-2,"1 Day Late",IF(I2=TODAY()-1,"1 Day Late",IF(I2=TODAY()-4,"2 Days Late",IF(I2<TODAY(),"OLD",IF(I2=TODAY()+1,"Tomorrow",IF(I2=TODAY()+2,"2 Days Out","Future"))))))))
Tuesday	=IF(I2=TODAY(),"TODAY",IF(I2=TODAY()-1,"1 Day Late",IF(I2=TODAY()-2,"2 Days Late",IF(I2=TODAY()-3,"2 Days Late",IF(I2=TODAY()-4,"2 Days Late",IF(I2<TODAY(),"OLD",IF(I2=TODAY()+1,"Tomorrow",IF(I2=TODAY()+2,"2 Days Out","Future"))))))))
Wed	=IF(I2=TODAY(),"TODAY",IF(I2=TODAY()-1,"1 Day Late",IF(I2=TODAY()-2,"2 Days Late",IF(I2<TODAY(),"OLD",IF(I2=TODAY()+1,"Tomorrow",IF(I2=TODAY()+2,"Friday","Future"))))))
Thurs	=IF(I2=TODAY(),"TODAY",IF(I2=TODAY()-1,"1 Day Late",IF(I2=TODAY()-2,"2 Days Late",IF(I2<TODAY(),"OLD",IF(I2=TODAY()+1,"Friday",IF(I2=TODAY()+2,"Monday",IF(I2=TODAY()+3,"Monday",IF(I2=TODAY()+4,"Monday","Future"))))))))
Fri	=IF(I2=TODAY(),"TODAY",IF(I2=TODAY()-1,"1 Day Late",IF(I2=TODAY()-2,"2 Days Late",IF(I2<TODAY(),"OLD",IF(I2=TODAY()+1,"Monday",IF(I2=TODAY()+2,"Monday",IF(I2=TODAY()+3,"Monday",IF(I2=TODAY()+4,"Tuesday","Future"))))))))

I know I can string these all together, but there's got to be a simpler method. Would like one statement to cover all days of the week.

Suggestions?

Thanks!

 

[Scott Huish]

It's hard to figure out this whole weekend thing from your formulas, can you give some examples and expected results?

 

[sriche01]

-2​	2 days after today
-1​	1 day after today
0​	today
1​	1 day late
2​	2 days late

if the above range is pasted into A1 then copy this formula into the cell you want your result to be.
=IFS(TODAY()-I2>2,"Older",TODAY()-I2<-2,"Future",I2=I2,INDEX(B1:B5,MATCH(TODAY()-I2-IF(WEEKDAY(I2)=7,1,0),A1:A5,0)))
This is assuming ship date is still in I2.

 

[jzibton]

I'll be glad to elaborate, hopefully clearly . . .

In my open order file, Column 'I' is the ship date. On each day of the week, I grab the if statement designed specifically for that day of the week. So on Monday I pull the data and copy into column 'J' the if statement for Monday, on Tuesday I grab the IF statement that is specific for Tuesday, etc, etc.

Wednesday is the cleanest day since 'today-2 = Monday' and 'Today+2 = Friday'.

Below is each day, with each IF statement broken out. I did this for myself to make my original statements. To deal with Sat & Sun, I look at it if they were Monday or Friday. Sounds much more confusing that it actually is.

What I am hoping to do is simplify the IF Statements and potentially, through some VB magic, just have one statement that covers Mon-Fri. So I don't have to copy and paste everyday.

Monday

=IF(I2=TODAY(),"TODAY",

IF(I2=TODAY()-3,"1 Day Late",

IF(I2=TODAY()-2,"1 Day Late",

IF(I2=TODAY()-1,"1 Day Late",

IF(I2=TODAY()-4,"2 Days Late",

IF(I2<TODAY(),"OLD",

IF(I2=TODAY()+1,"Tomorrow",

IF(I2=TODAY()+2,"2 Days Out",

Future

Tuesday

=IF(I2=TODAY(),"TODAY",

IF(I2=TODAY()-1,"1 Day Late",

IF(I2=TODAY()-2,"2 Days Late",

IF(I2=TODAY()-3,"2 Days Late",

IF(I2=TODAY()-4,"2 Days Late",

IF(I2<TODAY(),"OLD",

IF(I2=TODAY()+1,"Tomorrow",

IF(I2=TODAY()+2,"2 Days Out",

Future

Wed

=IF(I2=TODAY(),"TODAY",

IF(I2=TODAY()-1,"1 Day Late",

IF(I2=TODAY()-2,"2 Days Late",

IF(I2<TODAY(),"OLD",

IF(I2=TODAY()+1,"Tomorrow",

IF(I2=TODAY()+2,"Friday",

Future

Thurs

=IF(I2=TODAY(),"TODAY",

IF(I2=TODAY()-1,"1 Day Late",

IF(I2=TODAY()-2,"2 Days Late",

IF(I2<TODAY(),"OLD",

IF(I2=TODAY()+1,"Tomorrow",

IF(I2=TODAY()+2,"Monday",

IF(I2=TODAY()+3,"Monday",

IF(I2=TODAY()+4,"Monday",

Future

Fri

=IF(I2=TODAY(),"TODAY",

IF(I2=TODAY()-1,"1 Day Late",

IF(I2=TODAY()-2,"2 Days Late",

IF(I2<TODAY(),"OLD",

IF(I2=TODAY()+1,"Monday",

IF(I2=TODAY()+2,"Monday",

IF(I2=TODAY()+3,"Monday",

IF(I2=TODAY()+4,"Tuesday",

Future

 

[Eric W]

Maybe something like this:

=IF(I2=TODAY(),"TODAY",ABS(NETWORKDAYS(I2,TODAY()))-1&" Day(s) "&IF(I2<TODAY(),"Late","Out"))

 

[sriche01]

So, I tried copying and pasting my solution above, and it didn't paste correctly. This formula avoids the data table.

=IFS(TODAY()-I2>2,"Older",TODAY()-I2<-2,"Future",I2=I2,INDEX({"2 days after today";"1 day after today";"today";"1 day late";"2 days late"},MATCH(TODAY()-I2-IF(WEEKDAY(I2)=7,1,0),{-2;-1;0;1;2},0)))

Do Either my or Eric's solutions get close?

 

[jzibton]

I've been trying to get back to this all day, and other 'stuff' has gotten in the way. Hope to respond over the weekend!
Thanks for input.

 

[jzibton]

Ok, I'm back. Eric's work! But the =IFS statement returns a #NAME? error. Probably a simple error but I couldn't find it.

Pretty sure I can use Eric's, so your choice if you want to go further.

Thanks for your assistance!

 

[Fluff]

But the =IFS statement returns a #NAME? error.

The IFS function is only available with 365 & 2019, so if you are using an older version you don't have it.

You might want to update your profile to show what version of Xl you are using.

 

[Eric W]

Glad we could help!