?
Solved

Workdays in a month based on 6 day work weeks

Posted on 2013-01-31
5
Medium Priority
?
440 Views
Last Modified: 2013-01-31
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
Comment
Question by:jmkbrown
  • 2
  • 2
5 Comments
 
LVL 7

Expert Comment

by:karunamoorthy
ID: 38839497
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
 

Author Comment

by:jmkbrown
ID: 38839549
Yes I would want 4 days in that week.
0
 
LVL 7

Expert Comment

by:karunamoorthy
ID: 38839706
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 38839812
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
 

Author Comment

by:jmkbrown
ID: 38840243
barryhoudini your formula works perfectly!  Thank you very much!
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question