Calculating Dates in Excel 2003

[Craig4670]

Hello:

Is it possible to calculate dates for a work week of Monday-Friday (excluding Sat and Sun) without using the NetWorkDays formula?

Not everyone in our office has the add-ins analysis tool pack installed on their computers - so the function does not work as it should.

For example:

workdays 9/15/11 - 9/19/11 (minus sat & sun)
total days worked would be 9/15, 9/16, 9/19

Any help would be greatly appreciated.

Thanks!

 

[indiantrix]

Unless I miss my guess, all they should need to do is click on Tools | Add-Ins and simply place a checkmark in the Analysis ToolPak box.
Your "solution" could be just that simple. Hope I'm correct. By default, all new installs of Excel 2003 had that box UNchecked...If you wanted it, you had to check the box "or else...". Anyone else?
Larry.

 

[T. Valko]

Hello:

Is it possible to calculate dates for a work week of Monday-Friday (excluding Sat and Sun) without using the NetWorkDays formula?

Not everyone in our office has the add-ins analysis tool pack installed on their computers - so the function does not work as it should.

For example:

workdays 9/15/11 - 9/19/11 (minus sat & sun)
total days worked would be 9/15, 9/16, 9/19

Any help would be greatly appreciated.

Thanks!

One way...

A2 = start date
B2 = end date
C2:C10 = holiday dates to be excluded from the count

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)<6),--(ISNA(MATCH(ROW(INDIRECT(A2&":"&B2)),C2:C10,0))))

If you don't need to account for holidays...

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)<6))

 

[AlphaFrog]

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Start Date</td><td style="font-weight: bold;text-align: center;;">End Date</td><td style="font-weight: bold;text-align: center;;">Work Days w/ Holidays</td><td style="font-weight: bold;text-align: center;;">Work Days w/o Holidays</td><td style="font-weight: bold;text-align: center;;">Holidays</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">8/15/2011</td><td style="text-align: center;;">9/19/2011</td><td style="text-align: center;;">24</td><td style="text-align: center;;">26</td><td style="text-align: center;;">9/5/2011</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">9/16/2011</td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=SUM(<font color="Blue">INT(<font color="Red">(<font color="Green">WEEKDAY(<font color="Purple">A2-{2,3,4,5,6}</font>)+B2-A2</font>)/7</font>)</font>) - SUMPRODUCT(<font color="Blue">--(<font color="Red">Holidays>=A2</font>),--(<font color="Red">Holidays<=B2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=SUM(<font color="Blue">INT(<font color="Red">(<font color="Green">WEEKDAY(<font color="Purple">A2-{2,3,4,5,6}</font>)+B2-A2</font>)/7</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Holidays</th><td style="text-align:left">=Sheet1!$E$2:$E$20</td></tr></tbody></table></td></tr></table><br />

 

[Craig4670]

Thank You for your help!

 

[Craig4670]

Unless I miss my guess, all they should need to do is click on Tools | Add-Ins and simply place a checkmark in the Analysis ToolPak box.
Your "solution" could be just that simple. Hope I'm correct. By default, all new installs of Excel 2003 had that box UNchecked...If you wanted it, you had to check the box "or else...". Anyone else?
Larry.

Thank you for your help!

 

[Craig4670]

Thanks for your help!

 

[T. Valko]

Thanks for your help!

You're welcome!

 

[indiantrix]

Glad to offer some help. Looks like others "did the trick" for you. That's always good when the answer offered actually works.
Larry.

 

[Biz]

One way...

A2 = start date
B2 = end date
C2:C10 = holiday dates to be excluded from the count

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)<6),--(ISNA(MATCH(ROW(INDIRECT(A2&":"&B2)),C2:C10,0))))

If you don't need to account for holidays...

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)<6))

Biff,

Nice formula

Biz