How to Calculate Dates without counting the weekends

I need the formula to calculate dates (Mondy to Friday) without counting the
weekends

In article <[email protected]>,
Lillian F <Lillian [email protected]> wrote:

> I need the formula to calculate dates (Mondy to Friday) without counting the
> weekends

You can use the NETWORKDAYS function...

=NETWORKDAYS(StartDate,EndDate,Holidays)

You'll need to enable the AnalysisToolpak...

Tools > Add-Ins > and check AnalysisToolpak.

Hope this helps!

On Sat, 22 Jan 2005 11:25:01 -0800, Lillian F <Lillian
[email protected]> wrote:

>I need the formula to calculate dates (Mondy to Friday) without counting the
>weekends

Use HELP to look at the NetWorkdays function
--ron

And if you can't use NETWORKDAYS because your girlfriend
can't find the Office install CD for her home PC and thus
you can't install the Analysis ToolPak add-in, try:

=SUMPRODUCT(--(LEFT(TEXT(ROW(INDIRECT(J1&":"&K1)),"ddd"))
<>"S"))

;-)
HTH
Jason
Atlanta, GA

>-----Original Message-----
>I need the formula to calculate dates (Mondy to Friday)
without counting the
>weekends
>.
>

Typical bloke, blame the girlfriend :-). Anyway, Lillian could be a female
name as well as a guy's.

Nice formula though.

Bob


"Jason Morin" <[email protected]> wrote in message
news:[email protected]...
> And if you can't use NETWORKDAYS because your girlfriend
> can't find the Office install CD for her home PC and thus
> you can't install the Analysis ToolPak add-in, try:
>
> =SUMPRODUCT(--(LEFT(TEXT(ROW(INDIRECT(J1&":"&K1)),"ddd"))
> <>"S"))
>
> ;-)
> HTH
> Jason
> Atlanta, GA
>
> >-----Original Message-----
> >I need the formula to calculate dates (Mondy to Friday)
> without counting the
> >weekends
> >.
> >

Jason,

That's a neat formula, and it has the advantage over NETWORKDAYS of not
mattering which order you put the dates in (that has always been an
annoyance of NETWORKDAYS to me). I thought it would be improved a bit if you
used

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(J1&":"&K1)),2)<6))

as it doesn't have to do any string matching then, and it removes one nested
function. Furthermore, it can omit Fridays more easily if you test for < 5,
or even use an array of allowable days, such as {3,4,5} (making by beautiful
UDF redundant :-)). The obvious disadvantage is that NETWORKDAYS can
exclude holidays, but I came up with this version to do the same

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(J1&":"&K1)),2)<6))-SUMPRODUCT(--(COUNTIF
(holidays,ROW(INDIRECT(J1&":"&K1)))>0),--(WEEKDAY(ROW(INDIRECT(J1&":"&K1)),2
)<6))

I know you are a formula ace, so I wondered if this can be simplified?

Bob


"Jason Morin" <[email protected]> wrote in message
news:[email protected]...
> And if you can't use NETWORKDAYS because your girlfriend
> can't find the Office install CD for her home PC and thus
> you can't install the Analysis ToolPak add-in, try:
>
> =SUMPRODUCT(--(LEFT(TEXT(ROW(INDIRECT(J1&":"&K1)),"ddd"))
> <>"S"))
>
> ;-)
> HTH
> Jason
> Atlanta, GA
>
> >-----Original Message-----
> >I need the formula to calculate dates (Mondy to Friday)
> without counting the
> >weekends
> >.
> >

Bob-

Thanks. Actually, I was going to respond using your exact
formula that uses the typical WEEKDAY method, but decided
to be a little different. You're right though - it does
add another nested function. Harlan Grove would have a fit!
Sometimes I just want to try something new and creative,
even if it is a little less efficient. I think it's
important to explore new methods. While they initially may
be cumbersome and less efficient than the typical "canned"
solution, they are often improved by others (that's why I
love the NGs).

I'm leaving for a party, so I don't have time to play with
the holiday portion, but I like your formula - it's
probably what I would have arrived at. But I'll work on it
later.

As for the girlfriend, using her PC at her house kills me.
Windows 98 (ah!), Excel 2000, and no install CD, and no
VBA help file.

Regards,

Jason

PS - I'm still waiting for those white papers in
your "coming soon" section on your home page, esp. the VBA
Best Practices. I still need a lot of help on my VBA
skills!

>-----Original Message-----
>Jason,
>
>That's a neat formula, and it has the advantage over
NETWORKDAYS of not
>mattering which order you put the dates in (that has
always been an
>annoyance of NETWORKDAYS to me). I thought it would be
improved a bit if you
>used
>
>=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(J1&":"&K1)),2)<6))
>
>as it doesn't have to do any string matching then, and it
removes one nested
>function. Furthermore, it can omit Fridays more easily if
you test for < 5,
>or even use an array of allowable days, such as {3,4,5}
(making by beautiful
>UDF redundant :-)). The obvious disadvantage is that
NETWORKDAYS can
>exclude holidays, but I came up with this version to do
the same
>
>=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(J1&":"&K1)),2)<6))-
SUMPRODUCT(--(COUNTIF
>(holidays,ROW(INDIRECT(J1&":"&K1)))>0),--(WEEKDAY(ROW
(INDIRECT(J1&":"&K1)),2
>)<6))
>
>I know you are a formula ace, so I wondered if this can
be simplified?
>
>Bob
>
>
>"Jason Morin" <[email protected]> wrote
in message
>news:[email protected]...
>> And if you can't use NETWORKDAYS because your girlfriend
>> can't find the Office install CD for her home PC and
thus
>> you can't install the Analysis ToolPak add-in, try:
>>
>> =SUMPRODUCT(--(LEFT(TEXT(ROW(INDIRECT
(J1&":"&K1)),"ddd"))
>> <>"S"))
>>
>> ;-)
>> HTH
>> Jason
>> Atlanta, GA
>>
>> >-----Original Message-----
>> >I need the formula to calculate dates (Mondy to Friday)
>> without counting the
>> >weekends
>> >.
>> >
>
>
>.
>

"Jason Morin" <[email protected]> wrote in message
news:[email protected]...

> Sometimes I just want to try something new and creative,
> even if it is a little less efficient. I think it's
> important to explore new methods. While they initially may
> be cumbersome and less efficient than the typical "canned"
> solution, they are often improved by others (that's why I
> love the NGs).

I agree with you on that. Open the door, and soemone will furnishg the room
:-). And as you say, the NGs are for exploring the options as well as
providing a solution.
>
> I'm leaving for a party, so I don't have time to play with
> the holiday portion, but I like your formula - it's
> probably what I would have arrived at. But I'll work on it
> later.

Lucky you, it's nearly bedtime here. Also, just spotted another thread on a
similar topic, and Domenic provided a formula which is the obvious extension
(read improvement) on mine, which I think is where you would also have taken
it. This is that formula

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(J1&":"&K1)),2<6)*(1-ISNUMBER(MATCH(ROW(IND
IRECT(J1&":"&K1)),holidays,0))))

which can also use the array of allowable day numbers.

> PS - I'm still waiting for those white papers in
> your "coming soon" section on your home page, esp. the VBA
> Best Practices. I still need a lot of help on my VBA
> skills!

Major life changes at the moment have pre-occupied me. I have about 6 papers
to complete, and 3 add-ins, so I need to buckle down :-).

Regards

Bob

Dear Bob, J1=1-Feb-05, K1=4-Feb0-5. Using the formula below,
I get 18 instead of 4. If 3-Feb-05 is entered in "holidays", I get 13.
What error returns those numbers. Note the the formula has an extra")" when
accepting MS suggested prompt over the post by you. Tks


=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(J1&":"&K1)),2<6)*(1-ISNUMBER(MATCH(ROW(INDIRECT(J1&":"&K1)),holidays,0)))))

Robert,

Got a bracket out of line. Try this version instead

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(J1&":"&K1)),2)<6)*(1-ISNUMBER(MATCH(ROW(IN
DIRECT(J1&":"&K1)),holidays,0))))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Robert" <[email protected]> wrote in message
news:[email protected]...
> Dear Bob, J1=1-Feb-05, K1=4-Feb0-5. Using the formula below,
> I get 18 instead of 4. If 3-Feb-05 is entered in "holidays", I get 13.
> What error returns those numbers. Note the the formula has an extra")"
when
> accepting MS suggested prompt over the post by you. Tks
>
>
>
=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(J1&":"&K1)),2<6)*(1-ISNUMBER(MATCH(ROW(IND
IRECT(J1&":"&K1)),holidays,0)))))
>

Robert,

Got a bracket out of line. Try this version instead

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(J1&":"&K1)),2)<6)*(1-ISNUMBER(MATCH(ROW(IN
DIRECT(J1&":"&K1)),holidays,0))))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Robert" <[email protected]> wrote in message
news:[email protected]...
> Dear Bob, J1=1-Feb-05, K1=4-Feb0-5. Using the formula below,
> I get 18 instead of 4. If 3-Feb-05 is entered in "holidays", I get 13.
> What error returns those numbers. Note the the formula has an extra")"
when
> accepting MS suggested prompt over the post by you. Tks
>
>
>
=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(J1&":"&K1)),2<6)*(1-ISNUMBER(MATCH(ROW(IND
IRECT(J1&":"&K1)),holidays,0)))))
>