stanleytheyak
Board Regular
- Joined
- Oct 10, 2008
- Messages
- 124
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.
=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.