Calculate number of weekdays from the first of the month to current date without NETWORKDAYS or WEEKDAY formulas.

[stanleytheyak]

Is there a way to calculate number of weekdays from the first of the month to current date without using the NETWORKDAYS or WEEKDAY formulas? I thought I had it with the formula below, but it only works for December.

=TRUNC(DAY(A2)/7,0)*5+(MOD(DAY(A2),7))

With this formula, this is what I get:

<table style="border-collapse: collapse; width: 264pt;" width="352" border="0" cellpadding="0" cellspacing="0"><col style="width: 181pt;" width="241"> <col style="width: 83pt;" width="111"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt; width: 181pt;" width="241" align="right" height="17">Saturday, December 27, 2008</td> <td class="xl63" style="width: 83pt;" width="111" align="right">21.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="right" height="17">Sunday, December 28, 2008</td> <td class="xl63" align="right">20.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="right" height="17">Monday, December 29, 2008</td> <td class="xl63" align="right">21.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="right" height="17">Tuesday, December 30, 2008</td> <td class="xl63" align="right">22.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="right" height="17">Wednesday, December 31, 2008</td> <td class="xl63" align="right">23.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="right" height="17">Thursday, January 01, 2009</td> <td class="xl63" align="right">1.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="right" height="17">Friday, January 02, 2009</td> <td class="xl63" align="right">2.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="right" height="17">Saturday, January 03, 2009</td> <td class="xl63" align="right">3.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="right" height="17">Sunday, January 04, 2009</td> <td class="xl63" align="right">4.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="right" height="17">Monday, January 05, 2009</td> <td class="xl63" align="right">5.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="right" height="17">Tuesday, January 06, 2009</td> <td class="xl63" align="right">6.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="right" height="17">Wednesday, January 07, 2009</td> <td class="xl63" align="right">5.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="right" height="17">Thursday, January 08, 2009</td> <td class="xl63" align="right">6.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="right" height="17">Friday, January 09, 2009</td> <td class="xl63" align="right">7.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="right" height="17">Saturday, January 10, 2009</td> <td class="xl63" align="right">8.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="right" height="17">Sunday, January 11, 2009</td> <td class="xl63" align="right">9.00</td> </tr> </tbody></table>
The problem starts in January when it doesn't skip the weekend for some reason.

Any assistance here would be greatly appreciated.

 

[barry houdini]

If your date is in A2

=SUM(1+INT((DAY(A2)-WEEKDAY(A2-{1,2,3,4,5}))/7))

Edit: Sorry, that uses WEEKDAY, I doubt if you can do it without. I can understand you not wanting to use NETWORKDAYS as it's an add-in function but why not WEEKDAY?

 

[Dave Patton]

Possibly
=SUM(INT((WEEKDAY(DATE(YEAR(A2),MONTH(A2),1)-{2,3,4,5,6})-DATE(YEAR(A2),MONTH(A2),1)+A2)/7))

Dec 27 would show 20
Dec 28 would show 20
Dec 29 would show 21

 

[stanleytheyak]

This comes really close, but I need to find a way to do it without the WEEKDAY function.

 

[stanleytheyak]

I'm sure the growing question on this is "Why do you not want to use WEEKDAY and NETWEEKDAY?" In all honesty, I don't have a good answer for you other than to say my boss doesn't like those functions... Confused? Join the club...

 

[barry houdini]

Well, this version is just a cheat really, using TEXT function to ascertain the weekday rather than WEEKDAY function

=SUMPRODUCT((LEFT(TEXT(A2-DAY(A2)+ROW(INDIRECT("1:"&DAY(A2))),"ddd"))<>"s")+0)

I'd still recommend the first version I posted though. That will work on any version of Excel and is shorter and more efficient.

 

[stanleytheyak]

Thank you!!!

 

[Dave Patton]

Apologies I used the Weekday
An alternative
- all dates listed in A
- with 0 in C6
- Date like Jan 1, 2009 in A7
- C7 =C6+OR(MOD(A7,7)={2,3,4,5,6})
- Copy down