Search:

# Counting weekends between dates - in Excel VB

Asked By: Diem    Date: Oct 07    Category: MS Office    Views: 1985

I'v got a task where I have support records that start on a date and
change state a number of times before they close out.

On a number of occasions the record comes into a particular que
which attracts a charge against that area. I need to calculate the
total TIME a record spends in a particular que during it's lifecycle.

The data is dumped for me in a flat file with multiple entries per
record for each change of state of the record.

My biggest problem is programmatically removing the weekend (fri
midnight to Sun midnight - 48 hrs) from the cycle. A record may be
left open on the Friday and closed on the Monday - but the weekend
48hrs must be removed from the total. A record may exist for a
number of weeks so this process may take place a number of times.

I've got a bit of an idea on how to do it but would like to put it
out there for others to have a think about.

Eventually I'd like to have an Excel doc that has a couple of
buttons and requests the location of the input file, processes it
and spits out a new file/report conversion with the calculations
done.

Share:

If you divide the number  of days by 7, then the integer (round-down) result
is the minimum number of whole weekends.

Then get the day of week of both the starting and ending dates  and look at
whether there is an extra weekend involved.

You imply that a period can't start  or end actually inside a weekend. If it
can, then this test for the extra weekend becomes a bit  more complex, but
it's still just for this one extra weekend. The main calculation is the
division of the days by 7.

If you can't have a period starting or ending inside a weekend, then
checking for weekend span is a simple matter of checking if the period start
day-of-week is greater than the period end day-of-week. Excel starts with
zero for Sunday and counts up.

The que runs on GMT and no-one works across the weekend officially.
The real problem  is the records  could stop and start  over the
weekend but is unlikely and the client chooses that 48 block as the
contractual 'white space' so records shouldn't attract penalties
(time) during this period. So in short - yes it is difficult to sort
out. I'll try to document what I've done as it comes together.

First, a clarification. Excel starts with 1 for Sunday, not zero, and runs
up to 7 for Saturday. I was thinking of a different language.

First thing to do, probably, is work out whether you have an exact number  of
weeks - simply check for a remainder from the division of the period by 7.
If there isn't a remainder, then you can just take the integer result of the
division as the number of weekends. If there is a remainder, you still use
the integer result as the number of weekends covered by whole weeks. You
only need to handle a possible partial-week weekend, or possibly just an
orphan Saturday or Sunday.

I suspect that the following will work:

- if the end day of week is the same as the start  day of week, then you have
an extra 24 hours if that day is a Sat or Sun

else

- if the end day of week is greater than the start day of week, then you
might have one or two weekend days - add 24 if the start day is Sun and an
extra 24 if the end day is Sat

else (the end day of week is less than the start day of week)

- you have a whole extra weekend (I think) - add 48

Didn't find what you were looking for? Find more on Counting weekends between dates - in Excel VB Or get search suggestion and latest updates.