Next weekday ...

S

StargateFan

I have cells that have this formula in them:

=IF($B1<>"",SUM(B1+1),"")

which just means I get a list of days incrementing from that start
date.

The list lists weekend days, though, as well. How do we get this to
reflect only weekdays, so that if B1 has, say,
Wed., May.26.2010 in it, rather than get this list in column B:

Thu., May.27.2010
Fri., May.28.2010
Sat., May.29.2010
Sun., May.30.2010
Mon., May.31.2010
Tue., Jun.01.2010
Wed., Jun.02.2010
Thu., Jun.03.2010
Fri., Jun.04.2010
Sat., Jun.05.2010
Sun., Jun.06.2010
Mon., Jun.07.2010
....

we get only the weekdays, like this:

Thu., May.27.2010
Fri., May.28.2010
Mon., May.31.2010
Tue., Jun.01.2010
Wed., Jun.02.2010
Thu., Jun.03.2010
Fri., Jun.04.2010
Mon., Jun.07.2010
Tue., Jun.08.2010
Wed., Jun.09.2010
Thu., Jun.10.2010
Fri., Jun.11.2010
....

Thanks. :eek:D
 
S

StargateFan

I have cells that have this formula in them:

=IF($B1<>"",SUM(B1+1),"")

which just means I get a list of days incrementing from that start
date.

The list lists weekend days, though, as well. How do we get this to
reflect only weekdays, so that if B1 has, say,
Wed., May.26.2010 in it, rather than get this list in column B:

Thu., May.27.2010
Fri., May.28.2010
Sat., May.29.2010
Sun., May.30.2010
Mon., May.31.2010
Tue., Jun.01.2010
Wed., Jun.02.2010
Thu., Jun.03.2010
Fri., Jun.04.2010
Sat., Jun.05.2010
Sun., Jun.06.2010
Mon., Jun.07.2010
...

we get only the weekdays, like this:

Thu., May.27.2010
Fri., May.28.2010
Mon., May.31.2010
Tue., Jun.01.2010
Wed., Jun.02.2010
Thu., Jun.03.2010
Fri., Jun.04.2010
Mon., Jun.07.2010
Tue., Jun.08.2010
Wed., Jun.09.2010
Thu., Jun.10.2010
Fri., Jun.11.2010
...

Thanks. :eek:D

Darn, sorry. I just realized that this is dealing with formulas and
not right group. Should I re-post to the regular Excel ng?

Thx.
 
R

Ron Rosenfeld

I have cells that have this formula in them:

=IF($B1<>"",SUM(B1+1),"")

which just means I get a list of days incrementing from that start
date.

The list lists weekend days, though, as well. How do we get this to
reflect only weekdays, so that if B1 has, say,
Wed., May.26.2010 in it, rather than get this list in column B:

Thu., May.27.2010
Fri., May.28.2010
Sat., May.29.2010
Sun., May.30.2010
Mon., May.31.2010
Tue., Jun.01.2010
Wed., Jun.02.2010
Thu., Jun.03.2010
Fri., Jun.04.2010
Sat., Jun.05.2010
Sun., Jun.06.2010
Mon., Jun.07.2010
...

we get only the weekdays, like this:

Thu., May.27.2010
Fri., May.28.2010
Mon., May.31.2010
Tue., Jun.01.2010
Wed., Jun.02.2010
Thu., Jun.03.2010
Fri., Jun.04.2010
Mon., Jun.07.2010
Tue., Jun.08.2010
Wed., Jun.09.2010
Thu., Jun.10.2010
Fri., Jun.11.2010
...

Thanks. :eek:D


=IF($B1<>"",WORKDAY(B1,1),"")

If you get a #NAME! error, look at HELP for the WORKDAY function for
instructions how to resolve it.

Also note, in HELP, the optional Holidays argument.
--ron
 
K

ker_01

I think I'd approach this with an If statement (aircode, just showing the
logic)

=IF($B1="","",if(AND(weekday($B1)<5,$B1<>""),$B1+1,$B1+3))

If the prior cell is blank, this cell is blank
If the prior cell's weekday (number) is less than Friday then add one day
If the prior cell's weekday is friday, add 3 days to get to Monday

Note: weekday settings can be changed (which day of the week is "1"), so
this formula may produce different results depending on the settings of the
machine you are running it on.

HTH,
Keith
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top