How to Calculate Dates without counting the weekends

[Lillian F]

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

 

[Domenic]

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!

 

[Ron Rosenfeld]

On Sat, 22 Jan 2005 11:25:01 -0800, Lillian F <Lillian

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

Use HELP to look at the NetWorkdays function
--ron

 

[Jason Morin]

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

 

[Bob Phillips]

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

Nice formula though.

Bob

 

[Bob Phillips]

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]

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!

 

[Bob Phillips]

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

 

[Robert]

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)))))

 

[Bob Phillips]

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)