Calculating Workdays

[nathansizemore]

Hello,
I am needing some help calculating the number of working days between two dates, with the number of working days per week being variable.
Not sure Excel has a built in function for this? I have checkout out weekday, workday.intl, and networkingdays, but they do not offer exactly what I am wanting. If -

• A1=Start Date
• A2=End Date
• A3=Number of Working days/week
  • 1=Mon
  • 4=Mon-Thurs
  • 7=Mon-Sun

If I did A2-A1, I could get the total number of days, but from that - how do I get the total number of those days that are working days, based on A3?

Thanks in advance for any help!
(Excel 2010)

 

[nathansizemore]

Just for the sake of anyone who may be searching for the same thing and wants the answer, what I figured out was use networkdays and have a separate sheet that contained all the Fridays for the next 5 years, all the Saturdays, and all the Sundays. Then I made it so that if(A3=4, networkdays(A1, A2, SELECTION_OF_FRIDAYS_SATURDAYS_AND_SUNDAYS), if(A3=5, networkdays(A1, A2, SAME_MINUS_FRIDAYS)......etc.

 

[Ron Coderre]

(edited to improve clarity)
Actually, since you are using Excel 2010, it's much easier than you think.
With
A1: start date....01-Dec-2012
A2: end date.....10-Dec-2012
A3: workdays code...

1 means (Mondays only)
4 means (Mon-Thu)
7 means (Mon-Sun...all days)
anything else means standard work week (Mon-Fri)

This regular formula returns the count of work ays in that date range:
A4: =NETWORKDAYS.INTL(A1,A2,IFERROR(INDEX({"0111111","0000111","0000000"},MATCH(A3,{1,4,7},0)),1))

Note: the "0111111" codes flag 0's for workdays and 1's for non-workdays.

With
A3: 1....A4 returns: 2...Dec 3 and 10 are Mondays
A3: 4....A4 returns: 5...Dec 3,4,5 and 10 are Mon-Thu days
A3: 7....A4 returns: 8...Dec 3 thru 10 are Mon-Sun days
A3: 2....A4 returns: 6...Dec 3 thru 7 and 10 are standard Mon-Fri days

Is that something you can use?

 

[nathansizemore]

Yes. That was a lot easier. Thanks!

 

[Armando Montes]

One more:
Number Of Weekdays Between Two Dates
For a count of weekdays between two dates without using NETWORKDAYS
=SUM(INT((WEEKDAY(A2-{2,3,4,5,6})+B2-A2)/7))