+ Reply to Thread
Results 1 to 19 of 19

How to calculate SLA (weekends included)

  1. #1
    Registered User
    Join Date
    08-12-2019
    Location
    Melbourne
    MS-Off Ver
    MS office pro plus 2019 -
    Posts
    14

    How to calculate SLA (weekends included)

    Hi everyone

    Can someone please help me?

    I was looking for ideal function to calculate SLA. There are post regarding SLA calculation only for Mon-Fr within operational hours.

    My problem is how to calculate SLA when SLA is met within 2 hours and where it is met within 48hrs

    Operational Hours:
    Mon-Fr 8:00 - 20:00
    Sat and Sunday 09:00 - 17:00



    For example:

    Case 1:

    If drop down 1, in column A1, is selected.

    SLA is met if action is taken within 2 hours.

    Case 2:

    If drop down 2, in column A2 is selected.

    SLA is met if action is taken within 48 hours.

    There may be more cases, i.e. with different drop down options but even if they have a different name, the "Case" type SLA times are the same. i.e. 3 drop down options are case 1 types, 2 are case 2 types.


    Any help at all is appreciated.

    Thank you

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,539

    Re: How to calculate SLA (weekends included)

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.

    Are you really using the old Excel 2010?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Registered User
    Join Date
    08-12-2019
    Location
    Melbourne
    MS-Off Ver
    MS office pro plus 2019 -
    Posts
    14

    Re: How to calculate SLA (weekends included)

    Hi AliGW - no, I have the most recent excel.

    Thanks for the welcome

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,539

    Re: How to calculate SLA (weekends included)

    OK, so you need to update your profile with whichever 'most recent' you have - either Excel 2019 (standalone) or MS365 (subscription).

    Please also supply us with a sample (desensitised) workbook, as requested.

    You will also need to explain the abbreviation SLA - you are assuming that it's commonly understood, but I have no idea what it stands for.
    Last edited by AliGW; 07-04-2021 at 05:12 AM.

  5. #5
    Registered User
    Join Date
    08-12-2019
    Location
    Melbourne
    MS-Off Ver
    MS office pro plus 2019 -
    Posts
    14

    Re: How to calculate SLA (weekends included)

    Hi AliGW - please see sample workbook attached

    Sorry- SLA means service level agreement time. As in by when the work needs to be completed.
    Attached Files Attached Files

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,539

    Re: How to calculate SLA (weekends included)

    Thanks. Please address the first and third points raised in my previous post.

  7. #7
    Registered User
    Join Date
    08-12-2019
    Location
    Melbourne
    MS-Off Ver
    MS office pro plus 2019 -
    Posts
    14

    Re: How to calculate SLA (weekends included)

    Sorry- updated my profile and also attached spreadsheet in the above reply

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,539

    Re: How to calculate SLA (weekends included)

    Sorry - your workbook sample is not adequate. You failed to follow the instructions, which say:

    Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results.
    And you still haven't explained SLA ...

  9. #9
    Registered User
    Join Date
    08-12-2019
    Location
    Melbourne
    MS-Off Ver
    MS office pro plus 2019 -
    Posts
    14

    Re: How to calculate SLA (weekends included)

    Sorry- SLA means service level agreement time. As in by when the work needs to be completed.

    I'll update my spreadsheet and will post it in a moment

  10. #10
    Registered User
    Join Date
    08-12-2019
    Location
    Melbourne
    MS-Off Ver
    MS office pro plus 2019 -
    Posts
    14

    Re: How to calculate SLA (weekends included)

    Please see attached updated excel sample workbook.

    I tried couldn't get it working with the weekends etc. I jerry rigged other formulas.

    I tried to keep the columns the same so as to keep the formula working.

    example of key dates and times are in column Q - V .


    Sorry about the start to this email. Hopefully I covered everything.

    Thank you for your patience
    Attached Files Attached Files

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,539

    Re: How to calculate SLA (weekends included)

    I am really sorry, but I don't think you are reading what I am asking you to do!

    Manually calculated results are just that - we aren't expecting you to work out the formulae yourself - that's what we will do once you have provided some MANUALLY CALCULATED RESULTS. And 'some' is a crucial word: not 'one'.

    Also your profile is confusing: are you using the subscription 365 version or the standalone Excel 2019? Or both? And if the latter, which of them are you using for this project? There are differences that may well affect the solutions offered.

  12. #12
    Registered User
    Join Date
    08-12-2019
    Location
    Melbourne
    MS-Off Ver
    MS office pro plus 2019 -
    Posts
    14

    Re: How to calculate SLA (weekends included)

    Sorry - please see updated manually calculated sample work sheet.

    Also I have updated by profile - just using office pro 2019.

    thank you. much appreciated.
    Attached Files Attached Files

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,539

    Re: How to calculate SLA (weekends included)

    Mmm. I can't see any manually calculated results that would involve anything more than looking up the case number and returning the value in the cell to its right. There must be more to it than this ...

    You really need to include two or three more complicated ones to show what you want (so ones that span multiple days and holidays). Or maybe it is just a simple lookup you need???

    Still not convinced you've given enough for anyone to have a stab at a formula - sorry.

  14. #14
    Registered User
    Join Date
    08-12-2019
    Location
    Melbourne
    MS-Off Ver
    MS office pro plus 2019 -
    Posts
    14

    Re: How to calculate SLA (weekends included)

    Hi AliGW, I need a lookup that takes into consideration the operating hours:

    Operational Hours:
    Mon-Fr 8:00 - 20:00
    Sat and Sunday 09:00 - 17:00

    So when case 1 is selected and something comes in 10 mins before closing time, it deducts 10 mins from the over all "Time to complete". then allocates the remaining time to to the start of the next day.

    The big issue is that sat and sunday have different start and finishing time

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,539

    Re: How to calculate SLA (weekends included)

    But I don't see where you have shown that in the sample workbook ...

  16. #16
    Registered User
    Join Date
    08-12-2019
    Location
    Melbourne
    MS-Off Ver
    MS office pro plus 2019 -
    Posts
    14

    Re: How to calculate SLA (weekends included)

    Sorry I am new to all of this.

    Did I not show it in CASE 1? of the sample workbook v3? it crosses from a weekday to a weekend.

    or case 3 - from weekend to weekday

  17. #17
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,830

    Re: How to calculate SLA (weekends included)

    There is a formula in row 8 down which the OP appears to have overwritten. Perhaps reinstate the formula (which appears to be a "standard" one for this type of problem) and there will a "starter" for us.

    Column C is column B with the SLA hours added, allowing for weekdays, weekends and daily working hours.

  18. #18
    Registered User
    Join Date
    08-12-2019
    Location
    Melbourne
    MS-Off Ver
    MS office pro plus 2019 -
    Posts
    14

    Re: How to calculate SLA (weekends included)

    Hi John, that is right. I had a formula in there before but it seems to only work on for weekdays and operates only 1 "Operating time"

    I need 2 operating times and weekends.


    here is the formula :



    Please Login or Register  to view this content.


    Please find attached updated spreadsheet.
    Attached Files Attached Files

  19. #19
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,063

    Re: How to calculate SLA (weekends included)

    This proposal employs eleven helper columns (D:N) which may be moved and/or hidden for aesthetic purposes.
    Column D is populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Columns E:N are populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The SLA Due column is populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    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] Need to calculate 10 weekends every year.
    By eeps24 in forum Excel General
    Replies: 8
    Last Post: 09-14-2018, 09:02 AM
  2. calculate dates excluding weekends
    By Axmed.cm in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-04-2017, 11:10 AM
  3. need to calculate max excluding the weekends
    By freak11 in forum Excel General
    Replies: 2
    Last Post: 08-23-2014, 03:30 PM
  4. Replies: 14
    Last Post: 05-28-2013, 02:06 PM
  5. Help: Determining TAT based on business hours... weekends included
    By dagbruinsfan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-07-2013, 10:34 AM
  6. Replies: 0
    Last Post: 12-14-2011, 09:29 AM
  7. how do I calculate rent over time with interest increase included
    By MarcApril in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-30-2006, 08:55 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