• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to calculate workdays between two dates if my weekends are Friday Saturday

"All that's necessary is that you always add one day to the first argument of the WORKDAY() function, and subtract one after the end of the function. The explanation is sort of complicated, but if you really want to know...


The WORKDAY() function assumes the weekend is Saturday/Sunday. When you add one to the date that's the first argument of the function, it moves your start day one day later, so relative to your start date, the weekend is one day earlier, that is, Friday/Saturday. Then outside the WORKDAY() function, you subtract one, so you balance out the day you added within the function, and get the correct answer."


Copied from:

http://en.allexperts.com/q/Excel-1059/Excel-weekend.htm
 
Pinkey


Firstly, Welcome to the Chandoo.org Forums


If your using Excel 2010 you can use the Workday.intl Function

=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

Use a Weekend number of 7 = (Friday & Saturday)


A1=start_date

A2=end_date

=NETWORKDAYS.INTL(A1, A2, 7, [holidays])

Holidays are optional
 
Interesting & elegant solution Luke...


You can also try the NETWORKINGDAYS() UDF - http://chandoo.org/wp/2009/06/09/networkingdays/
 
Thanks for the info Luke. I'm guessing this can be used to change the Theoretical weekends embedded within the function.
 
Networkdays.Intl allows the user to define any day as a "weekend"


It has 14 built in combinations of weekends

eg: Weekend number values indicate the following weekend days:


Weekend number Weekend days

1 or omitted Saturday, Sunday

2 Sunday, Monday

3 Monday, Tuesday

4 Tuesday, Wednesday

5 Wednesday, Thursday

6 Thursday, Friday

7 Friday, Saturday

11 Sunday only

12 Monday only

13 Tuesday only

14 Wednesday only

15 Thursday only

16 Friday only

17 Saturday only


As well as allowing a string to define custom weekends

eg: 1010100 Will define Monday, Wednesday and Friday as weekends

eg: 0101010 Will define Tuesday, Thursday and Saturday as weekends
 
You must log in or register to reply here.
Back
Top