I'm trying to come up with a function that will give me the number of working
days between two dates. Unless I'm missing something WORKDAYS and
NETWORKDAYS will not work because Saturday is considered a workday.
I came up with a UDF that works fine for two dates that are less than a week
apart using some information from C Pearson's website. Then I realized that
although it doesn't happen often I still need to allow for dates that will
span more than a week such that they could have multiple Sundays and Holidays
in them. That is where I'm stumped.
I'm not locked in to using a UDF if there is a worksheet formula that will
work.
Does anybody know how I would do this?
Thanks
Mike

I'm trying to come up with a function that will give me the number of
working
days between two dates. Unless I'm missing something WORKDAYS and
NETWORKDAYS will not work because Saturday is considered a workday.
I came up with a UDF that works fine for two dates that are less than a
week
apart using some information from C Pearson's website. Then I realized
that
although it doesn't happen often I still need to allow for dates that will
span more than a week such that they could have multiple Sundays and
Holidays
in them. That is where I'm stumped.

Here is a function I have posted in the compiled VB newsgroups (modified for
your requirement that Saturday is a workday) which calculates the number of
workdays (Monday thru Saturday) between any two dates, but it does not
account for Holidays (which vary by countries and, within the US, even by
individual states)...

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

It is fast because the maximum number of iteration in the loop is 6. The
holidays part of your question will have to be dealt with using a separate
loop. I would probably store the Holidays (within some maximum range of
dates) in an array and loop the array seeing if the individual Holiday dates
falls within, or on, the span covered by StartDate and EndDate and subtract
one for each date doing so.

Rick

Wow, that's impressive, and I learned about a new operator, the \.
If I understand it, you are using 6 days for each week up to the last one.
Then you are looping through the last week to see if there is a Sunday in the
date range. Pretty slick.

Your function gave me exactly what I asked for, but it exposed a fallicy in
my logic. I asked for the number of workdays thinking that would get me what
I needed. However, I now realize what I need is a different type of
calculation. For example if I start on 5/26 and end on 5/29 then it took me
2 days (not counting Sunday) to deliver the product. However if I start on
5/27 and end on 5/29 then it still took me 2 days since I started on Sunday.
Does that make sense?

I tried just subtracting 1 from the result if I start on a Sunday, but that
doesn't work. How would I change this to get the right result?

Thanks
Mike

"Rick Rothstein (MVP - VB)" wrote:

I'm trying to come up with a function that will give me the number of
working
days between two dates. Unless I'm missing something WORKDAYS and
NETWORKDAYS will not work because Saturday is considered a workday.
I came up with a UDF that works fine for two dates that are less than a
week
apart using some information from C Pearson's website. Then I realized
that
although it doesn't happen often I still need to allow for dates that will
span more than a week such that they could have multiple Sundays and
Holidays
in them. That is where I'm stumped.

Here is a function I have posted in the compiled VB newsgroups (modified for
your requirement that Saturday is a workday) which calculates the number of
workdays (Monday thru Saturday) between any two dates, but it does not
account for Holidays (which vary by countries and, within the US, even by
individual states)...

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

It is fast because the maximum number of iteration in the loop is 6. The
holidays part of your question will have to be dealt with using a separate
loop. I would probably store the Holidays (within some maximum range of
dates) in an array and loop the array seeing if the individual Holiday dates
falls within, or on, the span covered by StartDate and EndDate and subtract
one for each date doing so.

Rick

I think I got it! See below. I just add (not subtract) 1 to the startdate.

On the Holiday part. I was thinking I would create a isHoliday function and
use that in a loop as you described. I've already got the formulas for that.
I was wondering if there would be a way to do it that would be faster?

Thanks for your help.

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


"mikebres" wrote:

Wow, that's impressive, and I learned about a new operator, the \.
If I understand it, you are using 6 days for each week up to the last one.
Then you are looping through the last week to see if there is a Sunday in the
date range. Pretty slick.

Your function gave me exactly what I asked for, but it exposed a fallicy in
my logic. I asked for the number of workdays thinking that would get me what
I needed. However, I now realize what I need is a different type of
calculation. For example if I start on 5/26 and end on 5/29 then it took me
2 days (not counting Sunday) to deliver the product. However if I start on
5/27 and end on 5/29 then it still took me 2 days since I started on Sunday.
Does that make sense?

I tried just subtracting 1 from the result if I start on a Sunday, but that
doesn't work. How would I change this to get the right result?

Thanks
Mike

"Rick Rothstein (MVP - VB)" wrote:

I'm trying to come up with a function that will give me the number of
working
days between two dates. Unless I'm missing something WORKDAYS and
NETWORKDAYS will not work because Saturday is considered a workday.
I came up with a UDF that works fine for two dates that are less than a
week
apart using some information from C Pearson's website. Then I realized
that
although it doesn't happen often I still need to allow for dates that will
span more than a week such that they could have multiple Sundays and
Holidays
in them. That is where I'm stumped.

Here is a function I have posted in the compiled VB newsgroups (modified for
your requirement that Saturday is a workday) which calculates the number of
workdays (Monday thru Saturday) between any two dates, but it does not
account for Holidays (which vary by countries and, within the US, even by
individual states)...

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

It is fast because the maximum number of iteration in the loop is 6. The
holidays part of your question will have to be dealt with using a separate
loop. I would probably store the Holidays (within some maximum range of
dates) in an array and loop the array seeing if the individual Holiday dates
falls within, or on, the span covered by StartDate and EndDate and subtract
one for each date doing so.

Rick

Wow, that's impressive, and I learned about a new operator, the \.
If I understand it, you are using 6 days for each week up to the last one.
Then you are looping through the last week to see if there is a Sunday in
the
date range. Pretty slick.

Not sure about "slick", but yes, that is how the function works.


Your function gave me exactly what I asked for, but it exposed a fallicy
in
my logic. I asked for the number of workdays thinking that would get me
what
I needed. However, I now realize what I need is a different type of
calculation. For example if I start on 5/26 and end on 5/29 then it took
me
2 days (not counting Sunday) to deliver the product. However if I start
on
5/27 and end on 5/29 then it still took me 2 days since I started on
Sunday.
Does that make sense?

No, I am sorry, but it does not make sense. How can you start your job on a
Sunday if Sunday is not a workday?


Rick

One other question, what is the compiled VB newsgroup? Or better yet where
do I find them?

"Rick Rothstein (MVP - VB)" wrote:

I'm trying to come up with a function that will give me the number of
working
days between two dates. Unless I'm missing something WORKDAYS and
NETWORKDAYS will not work because Saturday is considered a workday.
I came up with a UDF that works fine for two dates that are less than a
week
apart using some information from C Pearson's website. Then I realized
that
although it doesn't happen often I still need to allow for dates that will
span more than a week such that they could have multiple Sundays and
Holidays
in them. That is where I'm stumped.

Here is a function I have posted in the compiled VB newsgroups (modified for
your requirement that Saturday is a workday) which calculates the number of
workdays (Monday thru Saturday) between any two dates, but it does not
account for Holidays (which vary by countries and, within the US, even by
individual states)...

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

It is fast because the maximum number of iteration in the loop is 6. The
holidays part of your question will have to be dealt with using a separate
loop. I would probably store the Holidays (within some maximum range of
dates) in an array and loop the array seeing if the individual Holiday dates
falls within, or on, the span covered by StartDate and EndDate and subtract
one for each date doing so.

Rick

One other question, what is the compiled VB newsgroup? Or better yet
where do I find them?

The compiled VB (Visual Basic) newsgroups are not Excel (nor VBA, the
non-compiled form of Visual Basic included in Office products like Excel)
oriented. There are significant differences between compiled VB and VBA, but
there are a lot of similarities too. If you are unfamiliar with the
differences between these forms of VB, code posted in a newsgroup devoted to
one of these forms of VB would normally be confusing to those only familiar
with the other form of VB. With that said, any of the microsoft.public
newsgroups having vb in their names would be for the compiled form of VB. As
an example, microsoft.public.vb.general.discussion is a fairly active
newsgroup.

Rick

Well, I work for the Postal Service, and while Sunday isn't a delivery day,
work is still being done on Sunday to get the mail delivered. We are using
sample mail to test the internal processes to make sure they are being
followed. Sometimes we start the mail on Sunday so I need to include that as
a possibility.

"Rick Rothstein (MVP - VB)" wrote:

Wow, that's impressive, and I learned about a new operator, the \.
If I understand it, you are using 6 days for each week up to the last one.
Then you are looping through the last week to see if there is a Sunday in
the
date range. Pretty slick.

Not sure about "slick", but yes, that is how the function works.


Your function gave me exactly what I asked for, but it exposed a fallicy
in
my logic. I asked for the number of workdays thinking that would get me
what
I needed. However, I now realize what I need is a different type of
calculation. For example if I start on 5/26 and end on 5/29 then it took
me
2 days (not counting Sunday) to deliver the product. However if I start
on
5/27 and end on 5/29 then it still took me 2 days since I started on
Sunday.
Does that make sense?

No, I am sorry, but it does not make sense. How can you start your job on a
Sunday if Sunday is not a workday?


Rick

So you mean, Sunday is valid, only if it is the StartDate, but should be
ignored as a workday otherwise ?

NickHK

"mikebres" wrote in message
...
Well, I work for the Postal Service, and while Sunday isn't a delivery
day,
work is still being done on Sunday to get the mail delivered. We are
using
sample mail to test the internal processes to make sure they are being
followed. Sometimes we start the mail on Sunday so I need to include that
as
a possibility.

"Rick Rothstein (MVP - VB)" wrote:

Wow, that's impressive, and I learned about a new operator, the \.
If I understand it, you are using 6 days for each week up to the last
one.
Then you are looping through the last week to see if there is a Sunday
in
the
date range. Pretty slick.

Not sure about "slick", but yes, that is how the function works.


Your function gave me exactly what I asked for, but it exposed a
fallicy
in
my logic. I asked for the number of workdays thinking that would get
me
what
I needed. However, I now realize what I need is a different type of
calculation. For example if I start on 5/26 and end on 5/29 then it
took
me
2 days (not counting Sunday) to deliver the product. However if I
start
on
5/27 and end on 5/29 then it still took me 2 days since I started on
Sunday.
Does that make sense?

No, I am sorry, but it does not make sense. How can you start your job
on a
Sunday if Sunday is not a workday?


Rick

I think I got it! See below. I just add (not subtract) 1 to the
startdate.

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

I don't think adding 1 to the StartDate (as you show) is the way to go.
Won't this give you a count that is one too few for StartDates that are not
Sunday? Also, I think my function will miscount by one if the StartDate is a
Saturday. What I think you need is to add the 1 only if the StartDate is a
Sunday, and not for any other StartDate. That could be done by adding this
line as the last line of my function...

If WeekDay(StartDate) = 1 Then WorkDays = WorkDays + 1

Rick

I believe that would work. Sorry, it's late and my mind is getting a bit
fuzzy. Here is an example; if I start on 5/19 (Saturday) and finished on
5/21, then I wouldn't count the day of 5/19, I would skip Sunday, and only
count Monday which would give me a one day delivery. See, it's impossible to
deliver on the same day of the pick up, so that day isn't counted. If I
started on Sunday then again I wouldn't count Sunday because it's the first
day and because it's Sunday for a one day delivery.
A more straight forward example; If I started on 5/21 (Monday) and finished
on 5/24 (Thursday) then I would only count Tuesday, Wednesday, and Thursday.
Yeah, I know it's a little crazy, but that's the way ot works.

Mike

"NickHK" wrote:

So you mean, Sunday is valid, only if it is the StartDate, but should be
ignored as a workday otherwise ?

NickHK

"mikebres" wrote in message
...
Well, I work for the Postal Service, and while Sunday isn't a delivery
day,
work is still being done on Sunday to get the mail delivered. We are
using
sample mail to test the internal processes to make sure they are being
followed. Sometimes we start the mail on Sunday so I need to include that
as
a possibility.

"Rick Rothstein (MVP - VB)" wrote:

Wow, that's impressive, and I learned about a new operator, the \.
If I understand it, you are using 6 days for each week up to the last
one.
Then you are looping through the last week to see if there is a Sunday
in
the
date range. Pretty slick.

Not sure about "slick", but yes, that is how the function works.


Your function gave me exactly what I asked for, but it exposed a
fallicy
in
my logic. I asked for the number of workdays thinking that would get
me
what
I needed. However, I now realize what I need is a different type of
calculation. For example if I start on 5/26 and end on 5/29 then it
took
me
2 days (not counting Sunday) to deliver the product. However if I
start
on
5/27 and end on 5/29 then it still took me 2 days since I started on
Sunday.
Does that make sense?

No, I am sorry, but it does not make sense. How can you start your job
on a
Sunday if Sunday is not a workday?


Rick

Right. I did run into problems with a Saturday Start.

So far this seems to work. I added a 1 on the startdate for the loop, then
subtracted 1 if StartDate is a Sunday.
Now all I have to do is put in a loop for the Holidays. I'll test it out
some more tomorrow, when I'm not so brain dead.

Thank you for your help!

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

"Rick Rothstein (MVP - VB)" wrote:

I think I got it! See below. I just add (not subtract) 1 to the
startdate.

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

I don't think adding 1 to the StartDate (as you show) is the way to go.
Won't this give you a count that is one too few for StartDates that are not
Sunday? Also, I think my function will miscount by one if the StartDate is a
Saturday. What I think you need is to add the 1 only if the StartDate is a
Sunday, and not for any other StartDate. That could be done by adding this
line as the last line of my function...

If WeekDay(StartDate) = 1 Then WorkDays = WorkDays + 1

Rick