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.

On Jun 25, 4:03*pm, ChrisP wrote:
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.

see this google search for some ideas

http://www.google.co.uk/search?sourc...+days+in+excel

=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))*(ho lidays<=MAX(end_date,start_date)))

--
HTH

Bob

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

"ChrisP" wrote in message
...
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.

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


"ChrisP" wrote in message
...
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.

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"ChrisP" wrote in message
...
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.

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)" wrote in
message ...
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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"ChrisP" wrote in message
...
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.

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


"Bob Phillips" wrote in message
...
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)" wrote in
message ...
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


"Rick Rothstein (MVP - VB)" wrote
in message ...
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


"ChrisP" wrote in message
...
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.