+ Reply to Thread
Results 1 to 2 of 2

Calculate SLA with two different working hours

  1. #1
    Registered User
    Join Date
    02-05-2020
    Location
    Malaysia
    MS-Off Ver
    2010
    Posts
    1

    Calculate SLA with two different working hours

    Hi, I need help on how to calculate SLA uptime in Excel that have two working hours.
    I have two different working hours in one day. For Example, in one day is working time is from 8.30am - 5.30pm and at night 10.30pm-6.30am. What formula to use combining this two different times? Also it exclude weekends and holidays.

    I used this formula but this is only for 1 working hours. I don't know how to combine with two working hours.
    =(NETWORKDAYS(A1,B1)-1)*("17:30"-"08:30")
    +IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),"17:30"
    ,"08:30"),"17:30")-MEDIAN(NETWORKDAYS(A1,A1)
    *MOD(A1,1),"17:30","08:30")


    Appreciate if you guys can assist me. Thank you.

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,848

    Re: Calculate SLA with two different working hours

    Not saying that I can solve, however it may make it easier for someone to help if you upload a sample .xlsx file. The instructions for uploading are in the banner at the top of the page. Please be sure to include manually calculated results so that we will have something on which to base formulas/code.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Calculate time for working hours when start time falls outside of working hours
    By SKDY_Beau in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-20-2014, 12:50 PM
  2. [SOLVED] How To Calculate Hours Between 2 or 3 Days Exclude Non Working Hours?
    By Fazrullah Jaini in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-03-2014, 09:54 AM
  3. [SOLVED] Calculate hours with predefined Working Hours and Weekend Hours
    By garciapliz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-05-2013, 11:17 AM
  4. Replies: 2
    Last Post: 06-14-2013, 10:45 AM
  5. Replies: 7
    Last Post: 03-31-2013, 03:09 PM
  6. Replies: 9
    Last Post: 03-08-2013, 11:50 AM
  7. Calculate working hours
    By imran91 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-02-2012, 06:22 AM
  8. Replies: 4
    Last Post: 11-30-2011, 03:25 PM

Tags for this Thread

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