+ Reply to Thread
Results 1 to 12 of 12

Need FORMULA for Date subtract Date equal bussiness hour (don't count weekend)

  1. 05-22-2015, 01:11 AM #1
    tuongtu3
    tuongtu3 is offline
    Forum Contributor
    Join Date
    08-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2007, MS 365 (Windows 10 Pro 64-bit)
    Posts
    818

    Need FORMULA for Date subtract Date equal bussiness hour (don't count weekend)

    Hello All,
    Is there a formula to subtract from date to date = hour but don't count weekend.

    5/21/15 4:53 PM - 5/25/15 4:53 PM = 48:00:00


    Regards,
    tt3
    Last edited by tuongtu3; 05-24-2015 at 03:29 AM. Reason: Solved
    Reply With QuoteRegister To Reply
  2. 05-22-2015, 01:24 AM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Need FORMULA for Date subtract Date equal bussiness hour (don't count weekend)

    How do you arrive at 48? 21 (friday) + 24 (Monday) + 25 (Tuesday) = 72

    A
    B
    C
    1
    5/21/2015 16:53
    5/25/2015 16:53
    72

    C1=NETWORKDAYS(A1,B1)*24
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 05-22-2015, 01:41 AM #3
    tuongtu3
    tuongtu3 is offline
    Forum Contributor
    Join Date
    08-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2007, MS 365 (Windows 10 Pro 64-bit)
    Posts
    818

    Re: Need FORMULA for Date subtract Date equal bussiness hour (don't count weekend)

    Hi FDibbins,
    But is there a formula to get result in Hours and Minutes?

    5/21/15 4:40 PM - 5/25/15 4:53 PM = 72:13


    Regards,
    tt3
    Reply With QuoteRegister To Reply
  4. 05-22-2015, 02:07 AM #4
    azumi
    azumi is offline
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 365
    Posts
    2,406

    Re: Need FORMULA for Date subtract Date equal bussiness hour (don't count weekend)

    How just B1-A1

    and custom formatted as [h]:mm
    Reply With QuoteRegister To Reply
  5. 05-22-2015, 02:33 AM #5
    tuongtu3
    tuongtu3 is offline
    Forum Contributor
    Join Date
    08-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2007, MS 365 (Windows 10 Pro 64-bit)
    Posts
    818
    Quote Originally Posted by azumi View Post
    How just B1-A1

    and custom formatted as [h]:mm
    does not work because I need to exclude weekend

    Regards,
    tt3
    Reply With QuoteRegister To Reply
  6. 05-22-2015, 02:37 AM #6
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Need FORMULA for Date subtract Date equal bussiness hour (don't count weekend)

    azumi, OP needs to exclude weekends.

    Try this...
    =(NETWORKDAYS(A1,B1))+MOD(B1-A1,1)
    formatted as Custom [h]:mm:ss;@
    Reply With QuoteRegister To Reply
  7. 05-22-2015, 09:53 PM #7
    tuongtu3
    tuongtu3 is offline
    Forum Contributor
    Join Date
    08-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2007, MS 365 (Windows 10 Pro 64-bit)
    Posts
    818

    Re: Need FORMULA for Date subtract Date equal bussiness hour (don't count weekend)

    Hi FDibbins,
    I tried your formula but it didn't work. Or I did something wrong here?

    Regards,
    tt3
    Reply With QuoteRegister To Reply
  8. 05-22-2015, 09:58 PM #8
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Need FORMULA for Date subtract Date equal bussiness hour (don't count weekend)

    What did it do, what did you get? Did you custom format the cell as I suggested?

    A
    B
    C
    1
    5/21/2015 15:33:00
    5/25/2015 16:53:40
    73:20:40

    C1=(NETWORKDAYS(A1,B1))+MOD(B1-A1,1)
    custom formatted [h]:mm:ss;@

    If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    Reply With QuoteRegister To Reply
  9. 05-23-2015, 02:59 AM #9
    tuongtu3
    tuongtu3 is offline
    Forum Contributor
    Join Date
    08-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2007, MS 365 (Windows 10 Pro 64-bit)
    Posts
    818

    Re: Need FORMULA for Date subtract Date equal bussiness hour (don't count weekend)

    Hi,
    Can you help me to apply your formula 
    Please Login or Register  to view this content.
    into below macro:


    My current is:
    Please Login or Register  to view this content.
    Please help me to modify by using your Formula:

    Please Login or Register  to view this content.
    Tried below but wrong:

    Please Login or Register  to view this content.
    Changes "Value" to "Address":
    Please Login or Register  to view this content.


    Regards,
    tt3
    Last edited by tuongtu3; 05-23-2015 at 06:25 PM.
    Reply With QuoteRegister To Reply
  10. 05-23-2015, 06:43 PM #10
    tuongtu3
    tuongtu3 is offline
    Forum Contributor
    Join Date
    08-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2007, MS 365 (Windows 10 Pro 64-bit)
    Posts
    818

    Re: Need FORMULA for Date subtract Date equal bussiness hour (don't count weekend)

    Quote Originally Posted by FDibbins View Post
    What did it do, what did you get? Did you custom format the cell as I suggested?

    A
    B
    C
    1
    5/21/2015 15:33:00
    5/25/2015 16:53:40
    73:20:40

    C1=(NETWORKDAYS(A1,B1))+MOD(B1-A1,1)
    custom formatted [h]:mm:ss;@

    If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    Still confuse why you count 73:20:40 hours while Saturday and Sunday are weekends then result should be 49:20:40 hours? Please explain

    Regards,
    tt3
    Reply With QuoteRegister To Reply
  11. 05-23-2015, 09:38 PM #11
    azumi
    azumi is offline
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 365
    Posts
    2,406

    Re: Need FORMULA for Date subtract Date equal bussiness hour (don't count weekend)

    This should be fix it:

    =(NETWORKDAYS(A1,B1,)-1)+MOD(B1,1)-MOD(A1,1)

    and formatted as [h]:mm:ss
    Reply With QuoteRegister To Reply
  12. 05-24-2015, 03:29 AM #12
    tuongtu3
    tuongtu3 is offline
    Forum Contributor
    Join Date
    08-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2007, MS 365 (Windows 10 Pro 64-bit)
    Posts
    818

    Re: Need FORMULA for Date subtract Date equal bussiness hour (don't count weekend)

    That's perfect. Thank you very much for your time and help.

    Regards,
    tt3
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. NETWORKDAYS with start date, end date, total days - if weekend subtract 1 from total
    By PConn31 in forum Excel Formulas & Functions
    Replies: 24
    Last Post: 10-08-2014, 10:31 AM
  2. Excel date without weekend count
    By Teuntja123 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-06-2014, 08:51 AM
  3. [SOLVED] Userform Textbox Date - Subtract Weekend
    By Nu2Java in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-12-2012, 05:14 PM
  4. Count by date and hour
    By ChristiaanV in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-11-2006, 07:30 PM
  5. [SOLVED] Cell colour chg when formula results equal a weekend day, i.e., "Saturday" and a date that falls on a Saturday?
    By StargateFanFromWork in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-19-2005, 08:15 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1