Weekday

  • Thread starter Thread starter Gee
  • Start date Start date
G

Gee

I have a simple formula that gives me 5 days from a
certain date (date+5) but I need it to only land on
weekdays, not weekends. What is the formula for that?
Thank you in advance.
Gee
 
That formula is adding 7 days onto the source date. I
need it to adjust to the proper date...as in if 5 days
from the date is on Sat or Sun it will adjust to Mon.
Any ideas?
g
 
Hi Gee

One way:
=A1+5+(WEEKDAY(A1,3)<1)+(WEEKDAY(A1,3)<2)

HTH. Best wishes Harald
 
Umm, try this see if it gives what you want. If A1+5 lands on a weekend, it
takes it over to Monday. For everything else it adds only 5 days (including
weekends).

=A1+5+(2*(WEEKDAY(A1+5)=7))+(WEEKDAY(A1+5)=1)
 
That formula is adding 7 days onto the source date. I
need it to adjust to the proper date...as in if 5 days
from the date is on Sat or Sun it will adjust to Mon.
Any ideas?
g
Click to expand...

What you seem to be asking for is what the add-in function WORKDAY does.

_|______A____________B_____..col B formulas
1| 3-May-2004 10-May-2004 =WORKDAY(A1,5)
2| 4-May-2004 11-May-2004 =WORKDAY(A2,5)
3| 5-May-2004 12-May-2004 =WORKDAY(A3,5)
4| 6-May-2004 13-May-2004 =WORKDAY(A4,5)
5| 7-May-2004 14-May-2004 =WORKDAY(A5,5)
6| 8-May-2004 14-May-2004 =WORKDAY(A6,5)
7| 9-May-2004 14-May-2004 =WORKDAY(A7,5)
8| 10-May-2004 17-May-2004 =WORKDAY(A8,5)

5 days from 3-May-2004 (Monday) is 8-May-2004 (Saturday), but WORKDAY adjusts
this to 10-May-2004 (Monday). Do you mean that 6-May-2004 (Thursday) should
correspond to 11-May-2004 (Tuesday) rather than 13-May-2004 (Thursday)? If so,

=A1+MAX(5,3-(WEEKDAY(A1,3)-4))

_|______A____________B_____..col B formulas
1| 3-May-2004 10-May-2004 =A1+MAX(5,3-(WEEKDAY(A1,3)-4))
2| 4-May-2004 10-May-2004 =A2+MAX(5,3-(WEEKDAY(A2,3)-4))
3| 5-May-2004 10-May-2004 =A3+MAX(5,3-(WEEKDAY(A3,3)-4))
4| 6-May-2004 11-May-2004 =A4+MAX(5,3-(WEEKDAY(A4,3)-4))
5| 7-May-2004 12-May-2004 =A5+MAX(5,3-(WEEKDAY(A5,3)-4))
6| 8-May-2004 13-May-2004 =A6+MAX(5,3-(WEEKDAY(A6,3)-4))
7| 9-May-2004 14-May-2004 =A7+MAX(5,3-(WEEKDAY(A7,3)-4))
8| 10-May-2004 17-May-2004 =A8+MAX(5,3-(WEEKDAY(A8,3)-4))

If this also isn't what you want, then pick any 8-day period, show the initial
dates and the corresponding '5 workdays later' dates.
 
Yep that worked...except now they're telling me they want
it to land on friday...not monday.
g
 
I have the addin, but it's still not working for me. I
put 5/4/04 into A1...with =WORKDAY(A1,5)in A2 and I get
5/11/04. If I put 5/7/04 into A1...with =WORKDAY(A1,2) in
A2 I get 5/11/04.
My hair is starting to fall out.
 
I have the addin, but it's still not working for me. I
put 5/4/04 into A1...with =WORKDAY(A1,5)in A2 and I get
5/11/04. If I put 5/7/04 into A1...with =WORKDAY(A1,2) in
A2 I get 5/11/04.
My hair is starting to fall out.
Click to expand...

Good. Baldness may teach you the lesson of percision and completeness in
communication. You should have shows an 8 day period of initial dates and the
corresponding adjusted '5 day later' dates.

So you mean both of these should result in Monday, 10-May-2004? If so, see my
follow-up to myself.

=WORKDAY(DATE(2004,5,4)+4,1) == 10-May-2004
=WORKDAY(DATE(2004,5,7)+1,1) == 10-May-2004

**NOTE** that I'm **NOT** finding 5 workdays later by using the initial date as
1st argument and 5 as 2nd argument. I'm adding ** 4 ** (one less than the target
of 5) days to the initial date and using that as the 1st argument to WORKDAY,
then using 1 as second argument.

If this still isn't what you want, it's up to **YOU** to provide an explicit and
complete example of what you're looking for.
 
Yep that worked...except now they're telling me they want
it to land on friday...not monday.
Click to expand...

Ah, details. Charge 'em extra for the change order.

=WORKDAY(A1+6,-1)
 
Good. Baldness may teach you the lesson of percision and completeness in
communication.
Click to expand...

Said Google:
Did you mean: precision

Sorry man, couldn't resist. People lost Nobel prizes for less, and you were
darn close to one this time :-).

Best wishes Harald
 
Back
Top