Thread: Changing working days

This post appeared on 7/11/2006.
I repeat it because the author didn't get the answer. I have the same problem.
"Excel count weekly off Saturday & Friday. But my case it is Friday &
Saturday. I want to define that while using networkdays and workday functions"

In my words, if today is Thursday, then WORKDAY (today(),1,0) will give not
the next (friday's) date but Sunday's.

Please help
Greg

Not sure if I follow you.

If A1 = thursday do you want to drag down a list of dates excluded Fri and Sat. If this is the case then put this formula into cell A2 and drag down

=IF(OR(WEEKDAY(A1+1)=6,WEEKDAY(A1+1)=7),A1+3,A1+1)

VBA Noob

If you want to use NETWORKDAYS and WORKDAY functions with Friday and Saturday weekends then use

=NETWORKDAYS(A1+1,B1+1)

where A1 is your start date and B1 your end date

and

=WORKDAY(C1+1,D1)-1

where C1 is your start date and D1 the number of workdays you wish to advance.

If you also have a holiday range to exclude then you can use these

=NETWORKDAYS(A1+1,B1+1,holidays+1)
=WORKDAY(C1+1,D1,holidays+1)-1

both of which need to be confirmed with CTRL+SHIFT+ENTER

It's not so simple.
For example Wednesday+3 will give Monday,
but I need Sunday

"VBA Noob" wrote:

>
> Not sure if I follow you.
>
> If A1 = thursday do you want to drag down a list of dates excluded Fri
> and Sat. If this is the case then put this formula into cell A2 and
> drag down
>
> =IF(OR(WEEKDAY(A1+1)=6,WEEKDAY(A1+1)=7),A1+3,A1+1)
>
> VBA Noob
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=566388
>
>

As the crane-fly said

=WORKDAY(A1+1,3)-1

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Greg" <Greg@discussions.microsoft.com> wrote in message
news:54DE410F-7C96-4F6B-970E-3C0210E6FC7E@microsoft.com...
> It's not so simple.
> For example Wednesday+3 will give Monday,
> but I need Sunday
>
> "VBA Noob" wrote:
>
> >
> > Not sure if I follow you.
> >
> > If A1 = thursday do you want to drag down a list of dates excluded Fri
> > and Sat. If this is the case then put this formula into cell A2 and
> > drag down
> >
> > =IF(OR(WEEKDAY(A1+1)=6,WEEKDAY(A1+1)=7),A1+3,A1+1)
> >
> > VBA Noob
> >
> >
> > --
> > VBA Noob
> > ------------------------------------------------------------------------
> > VBA Noob's Profile:
http://www.excelforum.com/member.php...o&userid=33833
> > View this thread:
http://www.excelforum.com/showthread...hreadid=566388
> >
> >

Hi Greg,

A1 Your date
A2 number of working days to add (Fridays and Saturdays being NO
working days)

Result:
=A1+A2+INT((A2-MOD(A1+A2-6,7)+7)/7)+INT((A2-MOD(A1+A2-7,7)+7)/7)+(MOD(A1+A2+INT((A2-MOD(A1+A2-6,7)+7)/7)+INT((A2-MOD(A1+A2-7,7)+7)/7),7)=6)*2+(MOD(A1+A2+INT((A2-MOD(A1+A2-6,7)+7)/7)+INT((A2-MOD(A1+A2-7,7)+7)/7),7)=0)

Please test it. This is just a quick and dirty derivative from
http://www.sulprobil.com/html/date_formulas.html

HTH,
Bernd

Hi Bob
Your formula is the best: nice, shortest and correct.
Thanks indeed
Greg

"Bob Phillips" wrote:

> As the crane-fly said
>
> =WORKDAY(A1+1,3)-1
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Greg" <Greg@discussions.microsoft.com> wrote in message
> news:54DE410F-7C96-4F6B-970E-3C0210E6FC7E@microsoft.com...
> > It's not so simple.
> > For example Wednesday+3 will give Monday,
> > but I need Sunday
> >
> > "VBA Noob" wrote:
> >
> > >
> > > Not sure if I follow you.
> > >
> > > If A1 = thursday do you want to drag down a list of dates excluded Fri
> > > and Sat. If this is the case then put this formula into cell A2 and
> > > drag down
> > >
> > > =IF(OR(WEEKDAY(A1+1)=6,WEEKDAY(A1+1)=7),A1+3,A1+1)
> > >
> > > VBA Noob
> > >
> > >
> > > --
> > > VBA Noob
> > > ------------------------------------------------------------------------
> > > VBA Noob's Profile:
> http://www.excelforum.com/member.php...o&userid=33833
> > > View this thread:
> http://www.excelforum.com/showthread...hreadid=566388
> > >
> > >
>
>
>