Results 1 to 4 of 4

Thread: Hours Between the Days

  1. #1
    Neophyte sonbarse's Avatar
    Join Date
    Sep 2019
    Posts
    3
    Articles
    0
    Excel Version
    2016

    Hours Between the Days



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I need to calculate the Total hours an Incident/Ticket been live in system. I have incident login date/time & incident closed date/time.
    Working days are : Saturday to Thursday (Friday is OFF day)
    Working Hours are : 7AM to 6PM

    I tried the NETWORKDAYS formula in array, but the formula by default uses Saturday/Sunday as weekends & hence my answer is wrong.
    Can some Expert help me to fix this

    Have added my worksheet in attachment.
    Expert please advise.

    Thanks in Advance.
    Attached Files Attached Files

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,673
    Articles
    0
    Excel Version
    2010 on Xubuntu
    NETWORKDAYS.INTL uses a complementary switch (16 in your case) for week ends different from sat/sun . If shifts always end e between start and end hours try

    =(NETWORKDAYS.INTL(C2;D2;16)-1)*("18:00"-"07:00")+MOD(D2;1)-MOD(C2;1) - replace semi colons with commas if necessary

    See https://support.office.com/en-us/article/networkdays-intl-function-a9b26239-4f20-46a1-9ab8-4e925bfd5e28

    Last edited by Pecoflyer; 2019-09-15 at 08:28 AM.
    Thank you Ken for this secure forum.

  3. #3
    Neophyte sonbarse's Avatar
    Join Date
    Sep 2019
    Posts
    3
    Articles
    0
    Excel Version
    2016
    Thanks For the Quick Response. The amendment in the Formula worked.
    Will Explore More on the new Learning :-)
    Thanks Once again.

  4. #4
    Neophyte sonbarse's Avatar
    Join Date
    Sep 2019
    Posts
    3
    Articles
    0
    Excel Version
    2016
    Hi.
    The above formula only works if the ticket is closed next working day. If the ticket is aged for say 4 days, the formula gives incorrect output.
    Can you please advise.

Posting Permissions

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