Formula to recognize the next business day

[excelnut1954]

Simply put, I want to enter a formula in A6, that will look at the date
in E6. If the date in E6 is the next business day, then I want to show
a message in A6. This h as to do with a shipping date.

Right now, I have a crude way of doing it.

=IF(E6<1," ",IF(E6<=Todays_date,"SEND NOW"," "))

The range Todays_date is a formula in A1 =NOW()+1 which
allows the message "Send Now" to show up on the report the day before,
so the shipments can be ready for the next day.

When the shipping date is on a Monday, I have to change the formula
above to read, =NOW+3, so I can print the report showing the message
on it.

I want to have the formula recognize that the weekend dates are not to
be considered, and that Friday + 1 = Monday.

Any simple solutions?

Thanks,
J.O.

 

[Max]

Try this in A6:

=IF(E6="","",IF(AND(WEEKDAY(TODAY()-3,2)=5,E6<=TODAY()-3),"SEND
NOW",IF(E6<=TODAY()-1,"SEND NOW","")))

 

[Harlan Grove]

Max wrote...

Try this in A6:

=IF(E6="","",IF(AND(WEEKDAY(TODAY()-3,2)=5,E6<=TODAY()-3),
"SEND NOW",IF(E6<=TODAY()-1,"SEND NOW","")))

....

The OP's logic for E6 Tu thru F is E6<=Todays_date, which resolves to
E6<=NOW()+1, which could be replaced by E6<=TODAY()+1. How does this
morph into E6<=TODAY()-1 without becoming an error?

The OP is running a report the workday before it's used, and presumably
is only running it on workdays. If holidays weren't an issue, this
could be simplified to

=IF(E6<=TODAY()+IF(WEEKDAY(TODAY(),2)=5,3,1),"SEND NOW","")

If holidays were an issue, then the best approach would be to install
the Analysis ToolPak and use it's WORKDAY function. With holidays in a
range named Holidays,

=IF(E6<=WORKDAY(TODAY(),1,Holidays),"SEND NOW","")

 

[Max]

.. How does this morph into E6<=TODAY()-1 without becoming an error?

It was my mistake. Thanks for correction & refinements, Harlan !

 

[excelnut1954]

Thanks for the help Max & Harlan. Much appeciated.
J.O.

 

[Max]

You're welcome J.O.!
Thanks for feedback ..