+ Reply to Thread
Results 1 to 6 of 6

How do I calculate work days excluding all Sundays with 2nd and 4th Saturday of month?

  1. #1
    Registered User
    Join Date
    04-26-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    23

    How do I calculate work days excluding all Sundays with 2nd and 4th Saturday of month?

    I find a lot on net… Pl someone help me...!

    I want to take difference between 2 dates which exclude all Sunday with 2nd and 4th Saturday of month.


    Thanks

    SAGAR

  2. #2
    Forum Contributor satputenandkumar0's Avatar
    Join Date
    11-08-2012
    Location
    Pune, India
    MS-Off Ver
    Office xp & Office 2007
    Posts
    398

    Re: How do I calculate work days excluding all Sundays with 2nd and 4th Saturday of month?

    pls attach sample file.

  3. #3
    Valued Forum Contributor mahju's Avatar
    Join Date
    11-27-2010
    Location
    Pakistan, Faisalabad
    MS-Off Ver
    Excel 2010 plus
    Posts
    730

    Re: How do I calculate work days excluding all Sundays with 2nd and 4th Saturday of month?

    Hi
    What will be your date range (spams to only one month or more then one month)?

    regards
    Mark the thread as solved if you are satisfied with the answer.


    In your first post under the thread tools.

    Mahju

  4. #4
    Registered User
    Join Date
    04-26-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: How do I calculate work days excluding all Sundays with 2nd and 4th Saturday of month?



    I am So Sorry to respond to this line.
    For eg, from 1st Jan till date I want to calculate workdays
    Rules: exclude all Sundays; exclude 2nd & 4th sat of every month

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: How do I calculate work days excluding all Sundays with 2nd and 4th Saturday of month?

    I think the simplest way to do this in Excel 2010 is to use NETWORKDAYS.INTL function to exclude Sundays automatically and build a list of 2nd/4th Saturdays and exclude those too as "holidays", e.g. with start date in A2 and end date in B2 use this formula

    =NETWORKDAYS.INTL(A2,B2,11,H$2:H$500)

    the 11 tells it to exclude Sundays - in H2 put the first Saturday e.g. 14-Jan-2012 and then use this formula in H3 copied down to get a list of all 2nd/4th Sats

    =IF(DAY(H2+14)<8,21,14)+H2

    Obviously you need to include as many Saturdays as you need to include all the date periods of interest.....

    If you really want you can build that holiday list in to the formula but it will make the formula more complex....
    Audere est facere

  6. #6
    Registered User
    Join Date
    04-26-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: How do I calculate work days excluding all Sundays with 2nd and 4th Saturday of month?

    I would be better to use list of holiday, which includes 2nd and 4th saturday, its simple to use networkdays.intl function.

    Thanks..

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1