+ Reply to Thread
Results 1 to 7 of 7

formula needed to calculate the difference between 2 dates excluding wkend

  1. #1
    Registered User
    Join Date
    04-07-2011
    Location
    Austin
    MS-Off Ver
    Excel 2003
    Posts
    1

    formula needed to calculate the difference between 2 dates excluding wkend

    Hello... I hoping someone can help me. I need a formula to calculate the difference between 2 dates excluding wkends. However, if a wkend is entered into to the start or end time, I would like the forumula to automatically start calculating fm Monday.

    I am currently using NETWORKDAYS(D2,E2,AZ2:BC2)-1-MOD(D2,1)+MOD(E2,1))... when a wkend date is entered the result is ####

    Thanks!
    Last edited by danielle16dl; 04-07-2011 at 01:29 PM. Reason: title not specific

  2. #2
    Valued Forum Contributor
    Join Date
    05-13-2010
    Location
    Belo Horizonte, Brazil
    MS-Off Ver
    Excel 2003; 2007
    Posts
    441

    Thumbs up Re: formula needed to calculate the difference between 2 dates excluding wkend

    Dear Danielle16dl, Good Evening.

    "...Hello... I hoping someone can help me. I need a formula to calculate the difference between 2 dates excluding wkends. However, if a wkend is entered into to the start or end time, I would like the forumula to automatically start calculating fm Monday..."
    The function that you choosed is perfect for this task.
    If the initial date is a weekend the function will skip these days and start counting on Monday automatically.

    "...I am currently using NETWORKDAYS(D2,E2,AZ2:BC2)-1-MOD(D2,1)+MOD(E2,1))..."
    This formula sounds SO strange to me.
    MOD(D2,1) will returns ALLWAYS ZERO (0)

    MOD(E2,1) will returns ALLWAYS ZERO (0)

    THEN, MINUS MOD(D2,1) PLUS MOD(E2,1) will returns ALLWAYS ZERO (0)

    WHY do you need this?


    "...when a wkend date is entered the result is ####..."
    This signal appears because you need to format the result cell to NUMBER and EXPAND the width of the column a little more. Just this.

    I believe that after these advices your doubts are gone, but if you prefer, you can save your worksheet at www.4shared.com and put the link here.

    Iīll help you as much as I can.
    ...If my answer helped you, Please, click on. * Add Reputation (at left)

    Best regards.
    Marc?lio Lob?o

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: formula needed to calculate the difference between 2 dates excluding wkend

    Quote Originally Posted by Mazzaropi View Post
    I believe that after these advices your doubts are gone, but if you prefer, you can save your worksheet at www.4shared.com and put the link here.

    Iīll help you as much as I can.
    This forum has the capacity to upload excel and zip documents directly. Please use the forum tools.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  4. #4
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: formula needed to calculate the difference between 2 dates excluding wkend

    MOD(D2,1) will returns ALLWAYS ZERO (0)
    Not quite. If D2 is a number with decimals, this formula will return the decimals.

  5. #5
    Valued Forum Contributor
    Join Date
    05-13-2010
    Location
    Belo Horizonte, Brazil
    MS-Off Ver
    Excel 2003; 2007
    Posts
    441

    Thumbs up Re: formula needed to calculate the difference between 2 dates excluding wkend

    Dear NBVC, Good Morning.

    "...This forum has the capacity to upload excel and zip documents directly. Please use the forum tools..."

    OK.

    I had not the intention to break the forumīs rules.
    I simply forgot this forumīs feature.

  6. #6
    Valued Forum Contributor
    Join Date
    05-13-2010
    Location
    Belo Horizonte, Brazil
    MS-Off Ver
    Excel 2003; 2007
    Posts
    441

    Thumbs up Re: formula needed to calculate the difference between 2 dates excluding wkend

    Dear teylyn, Good Morning.

    "...MOD(D2,1) will returns ALLWAYS ZERO (0)

    Not quite. If D2 is a number with decimals, this formula will return the decimals...
    "

    You're totally right.
    But, in this case I can see that the user has a DATE at D2 and at B2.
    I donīt know why these are necessary.

    I canīt understand what is the finality of this use.
    Could you, please, to explain this to me?

  7. #7
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: formula needed to calculate the difference between 2 dates excluding wkend

    But, in this case I can see that the user has a DATE at D2 and at B2.
    I can't see what's in the cells unless I see the workbook. There's no way of telling from the formula whether or not D2 and B2 have date/time values (decimals) or just dates (integers).

+ 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