software

At the top of my spreadsheet, I have the formula @today()
In Column B, I have a list of dates....
I'd like for an entire row to change color if the date in Column B is the
next business day from the @today() date.
How can I do this with conditional formatting?

One possible way:

Assume the dates start in B1 going down, select the whole range, do format
conditional formatting, formula is and use

=AND($B1-TODAY()=1,WEEKDAY($B1,2)lt;6)select a format to apply and click OK twice
--

Regards,

Peo Sjoblom

nwexcelsolutions.comquot;Brianquot; gt; wrote in message
...
gt; At the top of my spreadsheet, I have the formula @today()
gt; In Column B, I have a list of dates....
gt; I'd like for an entire row to change color if the date in Column B is the
gt; next business day from the @today() date.
gt; How can I do this with conditional formatting?
I think you want

=B1=WORKDAY(TODAY(),1)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Peo Sjoblomquot; gt; wrote in message
...
gt; One possible way:
gt;
gt; Assume the dates start in B1 going down, select the whole range, do format
gt; conditional formatting, formula is and use
gt;
gt; =AND($B1-TODAY()=1,WEEKDAY($B1,2)lt;6)
gt;
gt;
gt; select a format to apply and click OK twice
gt;
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt; quot;Brianquot; gt; wrote in message
gt; ...
gt; gt; At the top of my spreadsheet, I have the formula @today()
gt; gt; In Column B, I have a list of dates....
gt; gt; I'd like for an entire row to change color if the date in Column B is
the
gt; gt; next business day from the @today() date.
gt; gt; How can I do this with conditional formatting?
gt;
gt;
You are right but your formula won't work in conditional formatting since it
is part of the ATP add-in unless you refer to another cell first

=$B1=TODAY() CHOOSE(WEEKDAY(DATE(YEAR(TODAY()),MON TH(TODAY()),DAY(TODAY())),2),1,1,1,1,3,2,1)

will work albeit ugly--

Regards,

Peo Sjoblom

nwexcelsolutions.com

quot;Bob Phillipsquot; gt; wrote in message
...
gt;I think you want
gt;
gt; =B1=WORKDAY(TODAY(),1)
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Peo Sjoblomquot; gt; wrote in message
gt; ...
gt;gt; One possible way:
gt;gt;
gt;gt; Assume the dates start in B1 going down, select the whole range, do
gt;gt; format
gt;gt; conditional formatting, formula is and use
gt;gt;
gt;gt; =AND($B1-TODAY()=1,WEEKDAY($B1,2)lt;6)
gt;gt;
gt;gt;
gt;gt; select a format to apply and click OK twice
gt;gt;
gt;gt;
gt;gt;
gt;gt; --
gt;gt;
gt;gt; Regards,
gt;gt;
gt;gt; Peo Sjoblom
gt;gt;
gt;gt; nwexcelsolutions.com
gt;gt;
gt;gt;
gt;gt; quot;Brianquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; At the top of my spreadsheet, I have the formula @today()
gt;gt; gt; In Column B, I have a list of dates....
gt;gt; gt; I'd like for an entire row to change color if the date in Column B is
gt; the
gt;gt; gt; next business day from the @today() date.
gt;gt; gt; How can I do this with conditional formatting?
gt;gt;
gt;gt;
gt;
gt;
Peo,

Have I missed something, or have you over-egged that

=$B1=TODAY() CHOOSE(WEEKDAY(TODAY(),2),1,1,1,1,3,2 ,1)

or even

=$B1=TODAY() CHOOSE(WEEKDAY(TODAY()),1,1,1,1,1,3,2 )

I prefer to use the default forms where possible

Bob

quot;Peo Sjoblomquot; gt; wrote in message
...
gt; You are right but your formula won't work in conditional formatting since
it
gt; is part of the ATP add-in unless you refer to another cell first
gt;
gt;
=$B1=TODAY() CHOOSE(WEEKDAY(DATE(YEAR(TODAY()),MON TH(TODAY()),DAY(TODAY())),
2),1,1,1,1,3,2,1)
gt;
gt; will work albeit ugly
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt;
gt;
gt; quot;Bob Phillipsquot; gt; wrote in message
gt; ...
gt; gt;I think you want
gt; gt;
gt; gt; =B1=WORKDAY(TODAY(),1)
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Peo Sjoblomquot; gt; wrote in message
gt; gt; ...
gt; gt;gt; One possible way:
gt; gt;gt;
gt; gt;gt; Assume the dates start in B1 going down, select the whole range, do
gt; gt;gt; format
gt; gt;gt; conditional formatting, formula is and use
gt; gt;gt;
gt; gt;gt; =AND($B1-TODAY()=1,WEEKDAY($B1,2)lt;6)
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; select a format to apply and click OK twice
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt;
gt; gt;gt; Regards,
gt; gt;gt;
gt; gt;gt; Peo Sjoblom
gt; gt;gt;
gt; gt;gt; nwexcelsolutions.com
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;Brianquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; At the top of my spreadsheet, I have the formula @today()
gt; gt;gt; gt; In Column B, I have a list of dates....
gt; gt;gt; gt; I'd like for an entire row to change color if the date in Column B is
gt; gt; the
gt; gt;gt; gt; next business day from the @today() date.
gt; gt;gt; gt; How can I do this with conditional formatting?
gt; gt;gt;
gt; gt;gt;
gt; gt;
gt; gt;
gt;
gt;
LOL! My only defense that I did it very late in an uncomfortable hotel
after driving 200 miles

Peoquot;Bob Phillipsquot; gt; wrote in message
...
gt; Peo,
gt;
gt; Have I missed something, or have you over-egged that
gt;
gt; =$B1=TODAY() CHOOSE(WEEKDAY(TODAY(),2),1,1,1,1,3,2 ,1)
gt;
gt; or even
gt;
gt; =$B1=TODAY() CHOOSE(WEEKDAY(TODAY()),1,1,1,1,1,3,2 )
gt;
gt; I prefer to use the default forms where possible
gt;
gt; Bob
gt;
gt; quot;Peo Sjoblomquot; gt; wrote in message
gt; ...
gt;gt; You are right but your formula won't work in conditional formatting since
gt; it
gt;gt; is part of the ATP add-in unless you refer to another cell first
gt;gt;
gt;gt;
gt; =$B1=TODAY() CHOOSE(WEEKDAY(DATE(YEAR(TODAY()),MON TH(TODAY()),DAY(TODAY())),
gt; 2),1,1,1,1,3,2,1)
gt;gt;
gt;gt; will work albeit ugly
gt;gt;
gt;gt;
gt;gt; --
gt;gt;
gt;gt; Regards,
gt;gt;
gt;gt; Peo Sjoblom
gt;gt;
gt;gt; nwexcelsolutions.com
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;Bob Phillipsquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I think you want
gt;gt; gt;
gt;gt; gt; =B1=WORKDAY(TODAY(),1)
gt;gt; gt;
gt;gt; gt; --
gt;gt; gt; HTH
gt;gt; gt;
gt;gt; gt; Bob Phillips
gt;gt; gt;
gt;gt; gt; (remove nothere from email address if mailing direct)
gt;gt; gt;
gt;gt; gt; quot;Peo Sjoblomquot; gt; wrote in message
gt;gt; gt; ...
gt;gt; gt;gt; One possible way:
gt;gt; gt;gt;
gt;gt; gt;gt; Assume the dates start in B1 going down, select the whole range, do
gt;gt; gt;gt; format
gt;gt; gt;gt; conditional formatting, formula is and use
gt;gt; gt;gt;
gt;gt; gt;gt; =AND($B1-TODAY()=1,WEEKDAY($B1,2)lt;6)
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; select a format to apply and click OK twice
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; --
gt;gt; gt;gt;
gt;gt; gt;gt; Regards,
gt;gt; gt;gt;
gt;gt; gt;gt; Peo Sjoblom
gt;gt; gt;gt;
gt;gt; gt;gt; nwexcelsolutions.com
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; quot;Brianquot; gt; wrote in message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt; At the top of my spreadsheet, I have the formula @today()
gt;gt; gt;gt; gt; In Column B, I have a list of dates....
gt;gt; gt;gt; gt; I'd like for an entire row to change color if the date in Column B
gt;gt; gt;gt; gt; is
gt;gt; gt; the
gt;gt; gt;gt; gt; next business day from the @today() date.
gt;gt; gt;gt; gt; How can I do this with conditional formatting?
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;
gt;gt; gt;
gt;gt;
gt;gt;
gt;
gt;