+ Reply to Thread
Results 1 to 13 of 13

date function which did not include sunday

  1. #1
    Registered User
    Join Date
    12-22-2010
    Location
    Hong Kong
    MS-Off Ver
    2013
    Posts
    74

    date function which did not include sunday

    Hi,

    I am required to calculate the number of days between two dates exclude sunday. Can someone please provide me such formulae?

    Thanks
    Last edited by Harrold; 11-03-2011 at 08:32 PM.

  2. #2
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: date function which did not include sunday

    try this forumla
    Attached Files Attached Files
    Regards

    tom1977

    If You are satisfied with my solution click the small star icon on the left to say thanks.

  3. #3
    Registered User
    Join Date
    12-22-2010
    Location
    Hong Kong
    MS-Off Ver
    2013
    Posts
    74

    Re: date function which did not include sunday

    It looks complicated. Is there a simplier solution?

    Thanks

  4. #4
    Registered User
    Join Date
    12-22-2010
    Location
    Hong Kong
    MS-Off Ver
    2013
    Posts
    74

    Re: date function which did not include sunday

    Is there a simplier solution?

  5. #5
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,337

    Smile Re: date function which did not include sunday

    Hello Harold

    try this formula.
    excel 2007, 2010

    Please Login or Register  to view this content.
    note:
    B1 - start date
    B2 - End Date
    11 - Sunday only(you can choose )
    F2 - cell containing a special holiday that you want to Exclude
    Hole That Helps

    Similar with NETWORKDAYS Function in excel 2003
    Last edited by vlady; 11-02-2011 at 11:16 PM.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  6. #6
    Registered User
    Join Date
    12-22-2010
    Location
    Hong Kong
    MS-Off Ver
    2013
    Posts
    74

    Re: date function which did not include sunday

    Hi vlady,

    Thanks for your reply.

    If the end date is empty, how can i have the result display as N/A?

    Thanks again

  7. #7
    Registered User
    Join Date
    12-22-2010
    Location
    Hong Kong
    MS-Off Ver
    2013
    Posts
    74

    Re: date function which did not include sunday

    can someone please look into it?

    Thanks

  8. #8
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: date function which did not include sunday

    Please allow 24 hrs before bumping, you are not the only one waiting for help

    If it is urgent you can always try commercial help.

  9. #9
    Registered User
    Join Date
    12-22-2010
    Location
    Hong Kong
    MS-Off Ver
    2013
    Posts
    74

    Re: date function which did not include sunday

    thank you very much indeed

  10. #10
    Registered User
    Join Date
    09-08-2011
    Location
    Hong Kong
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: date function which did not include sunday

    Here you go:
    =NETWORKDAYS.INTL(B1,B2,11)


    Jeremy
    Last edited by arthurbr; 11-03-2011 at 10:42 AM. Reason: Remove com link

  11. #11
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: date function which did not include sunday

    Jeremy

    please refrain from adding links to commercial sites to your posts.

  12. #12
    Registered User
    Join Date
    12-22-2010
    Location
    Hong Kong
    MS-Off Ver
    2013
    Posts
    74

    Re: date function which did not include sunday

    How come sunday is treated as 11?

  13. #13
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: date function which did not include sunday

    Quote Originally Posted by Harrold View Post
    How come sunday is treated as 11?
    Different types of weekends have different codes, for example 1 is the standard Saturday/Sunday weekend, 11 is Sundays only - see help files for more

    Quote Originally Posted by Harrold View Post
    If the end date is empty, how can i have the result display as N/A?
    Try this version

    =IF(B2="","N/A",NETWORKDAYS.INTL(B1,B2,11))
    Audere est facere

+ 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