Weekday

[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

 

[Frank Kabel]

Hi
=WORKDAY(A1,5)

you need to install the Analysis Toolpak Add-in for this

 

[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

 

[Harald Staff]

Hi Gee

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

HTH. Best wishes Harald

 

[Dave R.]

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)

 

[Harlan Grove]

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

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.

 

[Gee]

Yep that worked...except now they're telling me they want
it to land on friday...not monday.
g

 

[Harlan Grove]

...
...

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

...

Ugh! Figured I'd see the light just after posting. If ATP installed, just use

=WORKDAY(A1+4,1)

 

[Guest]

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.

 

[Harlan Grove]

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.

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.

 

[Harlan Grove]

Yep that worked...except now they're telling me they want
it to land on friday...not monday.

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

=WORKDAY(A1+6,-1)

 

[Harald Staff]

Good. Baldness may teach you the lesson of percision and completeness in
communication.

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

 

[Harlan Grove]

...
...

Said Google:
Did you mean: precision

...

I clm 2 no XL, nt spllng.

 

[Harald Staff]

I clm 2 no XL, nt spllng.

Lol. Well, as long as it keeps you young, no problemo...