conditional format business day

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)<6)


select a format to apply and click OK twice



--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Brian" <[email protected]> wrote in message
news:[email protected]...
> 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?

I think you want

=B1=WORKDAY(TODAY(),1)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
news:%[email protected]...
> 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)<6)
>
>
> select a format to apply and click OK twice
>
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> http://nwexcelsolutions.com
>
>
> "Brian" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
>
>

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()),MONTH(TODAY()),DAY(TODAY())),2),1,1,1,1,3,2,1)

will work albeit ugly


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com




"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
>I think you want
>
> =B1=WORKDAY(TODAY(),1)
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
> news:%[email protected]...
>> 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)<6)
>>
>>
>> select a format to apply and click OK twice
>>
>>
>>
>> --
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>> http://nwexcelsolutions.com
>>
>>
>> "Brian" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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?
>>
>>
>
>

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

"Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
news:%[email protected]...
> 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()),MONTH(TODAY()),DAY(TODAY())),
2),1,1,1,1,3,2,1)
>
> will work albeit ugly
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> http://nwexcelsolutions.com
>
>
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%[email protected]...
> >I think you want
> >
> > =B1=WORKDAY(TODAY(),1)
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
> > news:%[email protected]...
> >> 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)<6)
> >>
> >>
> >> select a format to apply and click OK twice
> >>
> >>
> >>
> >> --
> >>
> >> Regards,
> >>
> >> Peo Sjoblom
> >>
> >> http://nwexcelsolutions.com
> >>
> >>
> >> "Brian" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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?
> >>
> >>
> >
> >
>
>

LOL! My only defense that I did it very late in an uncomfortable hotel
after driving 200 miles

Peo


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
> news:%[email protected]...
>> 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()),MONTH(TODAY()),DAY(TODAY())),
> 2),1,1,1,1,3,2,1)
>>
>> will work albeit ugly
>>
>>
>> --
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>> http://nwexcelsolutions.com
>>
>>
>>
>>
>> "Bob Phillips" <[email protected]> wrote in message
>> news:%[email protected]...
>> >I think you want
>> >
>> > =B1=WORKDAY(TODAY(),1)
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > (remove nothere from email address if mailing direct)
>> >
>> > "Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
>> > news:%[email protected]...
>> >> 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)<6)
>> >>
>> >>
>> >> select a format to apply and click OK twice
>> >>
>> >>
>> >>
>> >> --
>> >>
>> >> Regards,
>> >>
>> >> Peo Sjoblom
>> >>
>> >> http://nwexcelsolutions.com
>> >>
>> >>
>> >> "Brian" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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?
>> >>
>> >>
>> >
>> >
>>
>>
>
>