Alternative to NETWORKDAYS to accommodate 1, 2, 3, 4 and 5 day working weeks

[Billysarah]

Hey everyone

I have been building a KPI workbook for a client and was foolish enough to think that I had correctly calculated the number of Business Days in each Financial Year and Reporting Month by using the NETWORKDAYS formula. But I just realised that some employees will be working part time and could work anything from 1 to 5 days/week, so I need an alternative.

The best solution I can come up with is putting in check boxes for the user to indicate days of the week worked, and then using these to eliminate, say, all Mondays in a year/month from a separate count of days of the week in the reqd range, but it's not very elegant and doesn't give any flexibility in terms of weekdays worked over different weeks during the period (if they didn't work Monday one week and Tuesday the next that might yield a different result than if they didn't ever work Mondays). Maybe I've just answered my own question and there isn't really a neat way to do it but worth asking?!

A simplified version of my worksheet is:

[TABLE="class: grid, width: 505"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Employee A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Start date[/TD]
[TD]1/7/12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]End date [/TD]
[TD] 30/6/13 [/TD]
[TD]Contracted days worked per week[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Contracted hours worked per day[/TD]
[TD="align: right"]7.5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]Annual leave weeks[/TD]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]Public holidays in year[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]Business days[/TD]
[TD="align: right"]????[/TD]
[/TR]
</tbody>[/TABLE]

I can use NETWORKDAYS in this scenario as the employee is working a 5 day week but I need the Business Days calculation to be updated to reflect a change in Contracted days worked per week, in which case NETWORKDAYS will not work. I'm not worried about anything that automatically excludes public holidays and AL as I'm just tagging that onto the end of the bus days calculation part of the formula.

Any suggestions??
Sarah

 

[Rick Rothstein]

See the Workday2 UDF (user defined function) here...

Better Workday

While his example shows two days off, there is nothing in the code to prevent you from specifying 3, 4, 5 or 6 days off if you need that.

 

[Billysarah]

Thanks Rick I'll take a look - seems like it might help!