asked on

Workdays in a month based on 6 day work weeks

Our work week is Monday through Saturday. So, I need a formula in Excel 2003 that will calculate the number of workdays in the month based on 6 days in a work week. I will still need to exclude holidays.

karunamoorthy

You told your work starts on monday through saturday. then suppose the month starts on wednesday then for that week, can you take 4 days in that week(wed+thu+fri+sat) or whole 6 days in that week.

You can try this link to get more help

http://www.teachexcel.com/excel-help/excel-how-to.php?i=412138

jmkbrown

ASKER

Yes I would want 4 days in that week.

karunamoorthy

Adding days but excluding Sunday's

Just to exclude Sundays try

=IF(WEEKDAY(A1+B1)=1,1)+A1+B1

where A1 is start date and b1 contains the number of days to add

If you want to exclude holidays too then the formula becomes a little trickier. If you have holiday dates listed in the range H1:H10 you can use this formula

=MIN(IF(WEEKDAY(A1+B1+{0,1,2,3,4})<>1,IF(ISNA(MATCH(A1+B1+{0,1,2,3,4},H$1:H$10,0)),A1+B1+{0,1,2,3,4})))

This is an "array formula" that needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar. That will accommodate up to 4 successive Sundays/holidays

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jmkbrown

ASKER

barryhoudini your formula works perfectly! Thank you very much!