weekdays not using NETWORKDAYS

[ChrisP]

I have a spreadsheet that various different users have access to. I can't use
the "NETWORKDAYS" function because not everyone has that add-in loaded. How
can I calculate the number of workdays in a month? I have the beginning date
and end date of the month, I just need to calculate the number of workdays
between the two dates.

Thanks.

 

[aidan.heritage]

I have a spreadsheet that various different users have access to. I can'tuse
the "NETWORKDAYS" function because not everyone has that add-in loaded. How
can I calculate the number of workdays in a month? I have the beginning date
and end date of the month, I just need to calculate the number of workdays
between the two dates.

Thanks.

see this google search for some ideas

http://www.google.co.uk/search?sour...-46,RNWE:en&q=calculate+working+days+in+excel

 

[Bob Phillips]

=SUMPRODUCT(INT((MAX(end_date,start_date)-WEEKDAY(MAX(end_date,start_date)+1-{2;3;4;5;6})-MIN(end_date,start_date)+8)/7))

or with a holiday list

=SUMPRODUCT(INT((MAX(end_date,start_date)-WEEKDAY(MAX(end_date,start_date)+1-{2;3;4;5;6})-MIN(end_date,start_date)+8)/7))
-SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4;5;6},0))*(holidays>=MIN(end_date,start_date))*(holidays<=MAX(end_date,start_date)))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

Here is a function I've posted in the past (over in the compiled VB
newsgroups) that will return the number of non-weekend days between two
given dates...

Function WorkDays(StartDate As Date, EndDate As Date) As Long
Dim D As Date
Dim NumWeeks As Long
NumWeeks = (EndDate - StartDate) \ 7
WorkDays = NumWeeks * 5
For D = (StartDate + NumWeeks * 7) To EndDate
If (Weekday(D) Mod 6) <> 1 Then WorkDays = WorkDays + 1
Next
End Function

Note that it only works for Saturday and Sunday as the weekend and it has no
provision to account for holidays. I thought I had a routine that did
account for holidays, but I cannot find it in my personal archives at the
moment (if I do find it, I'll post it, so you may want to check back later
in the day).

Rick

 

[Rick Rothstein \(MVP - VB\)]

Hmm! I thought I was in the programming newsgroup when I answered your
question. When I came back to this thread and saw Bob's answer, I realized I
had been mistaken. Sorry if that caused any confusing for you.

In any event, if you are up for a User Defined Function (UDF), the function
I posted, when placed in a VBA Module, can be used from the worksheet and it
will work the same as a built-in function does. If you want to explore this
method but are unfamiliar with UDFs, here are the details on how to
implement it. Press Alt+F11 to get into the VBA editor; click Insert/Module
from its menu bar to open up the code window for a Module; and copy/paste
the WorkDays function (that I posted in my first response) into the code
window that appeared. Now, go back to the worksheet and put this in a cell..

=WorkDays(A1,A2)

where A1 is the start date and A2 is the end date... the formula will
display the number of non-weekend days between (and including) those dates.

Rick

 

[Bob Phillips]

I would post a function in programming and VBA in worksheetfunctions if I
thought it best Rick <bg>

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

Of course, and I have done that myself on occasion; however, for the OP's
question, I wasn't sure if the function solution was best or not (and, of
course, I originally envisioned it being called by other VB code when I
constructed it being that I thought I was in the programming newsgroup). As
a UDF, the routine is quick as far as the VB code goes, but that is
counteracted by the loss in running VB code at the worksheet level. True,
the SUMPRODUCT function is not a speed demon among the various worksheet
functions, but it is my understanding that even slow worksheet functions
have the speed advantage over even the quickest of UDFs.

Rick