• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 438
  • Last Modified:

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.
0
jmkbrown
Asked:
jmkbrown
  • 2
  • 2
1 Solution
 
karunamoorthyCommented:
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
0
 
jmkbrownAuthor Commented:
Yes I would want 4 days in that week.
0
 
karunamoorthyCommented:
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
0
 
barry houdiniCommented:
To count the number of workdays in a month you can put first day of month in A2 and use this formula in B2

=28-DAY(A2+31)-(DAY(A2+34)<WEEKDAY(A2-1))-SUMPRODUCT((H$2:H$9>=A2)*(H$2:H$9<=A2+31-DAY(A2+31)))

Assuming that H2:H9 contains holiday dates - see attached

regards, barry
count-workdays.xls
0
 
jmkbrownAuthor Commented:
barryhoudini your formula works perfectly!  Thank you very much!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now