PDA

View Full Version : Weekday subtraction



ProteanBeing
02-27-2008, 09:59 AM
I need to know how many weekdays have past between to dates using VBA code. Please help

Bob Phillips
02-27-2008, 10:04 AM
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start_date&":"&end_date)),2)<6))

ProteanBeing
02-27-2008, 10:36 AM
I pasted this directly into a macro and defined the two variables. I got a syntax error

Bob Phillips
02-27-2008, 10:37 AM
That is becuase it is a formula not VBA

ProteanBeing
02-27-2008, 10:39 AM
I need to know how many weekdays have past between to dates using VBA code. Please help

Can this be done in VBA.

MikeO
02-27-2008, 10:54 AM
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start_date&":"&end_date)),2)<6))


This equation made me realize that I have a lot to learn about worksheet functions. :confused:

XLD...would you mind educating some of us lesser-skilled individuals by giving a brief description of how those elements work together? I tried to piece it out into separate cells to figure out how it works, but it didn't work out right. Thanks.

Bob Phillips
02-27-2008, 05:01 PM
Okay, here goes.

It really is not that hard. The basic principle is to build an array of dates between the start dat and end date and use WEEKDAY to return the day {number of each. If we use WEEKDAY(date,2), the week starts on a Monday, so we can then easily check the dates for weekdays by using < 6.

The hard part is building the array of dates. But ... because Excel holds dates as the number of days since 1st Jan 1900, we can just build an array of date numbers and use that (today 27th Feb is day 39505. We can build this array by using ROW(INDIRECT(start_date&":"&end_date)), which builds that array of date numbers. This is then passed to WEEKDAY to get the days of the week for those dates. The test for <6 returns an array of TRUE for the weekdays, FALSE for the weekends. The -- transforms this array to 1s and 0s, which SUMPRODUCT then totals.

For example, suppose the start_date is 23th Feb and end_date is 27th Feb the
start_date&":"&end_date resolves to 39501:39505
ROW(INDIRECT(start_date&":"&end_date)) then resolves to {39501;39502;39503;39504;39505}
WEEKDAY(ROW(INDIRECT(start_date&":"&end_date)),2) resolves to {6;7;1;2;3}
WEEKDAY(ROW(INDIRECT(start_date&":"&end_date)),2)<6 resolves to {FALSE;FALSE;TRUE;TRUE;TRUE}
--(WEEKDAY(ROW(INDIRECT(start_date&":"&end_date)),2)<6) resolves to {0;0;1;1;1}
and SUMPRODUCT adds this up and gets 3, the number of weekdays in those dates.

MikeO
02-28-2008, 08:06 AM
xld, that's great! Thanks for the explanation. I'll be adding that to my small arsenal of knowledge.