Forum Discussion

Akshadaks's avatar
Akshadaks
Copper Contributor
Jul 24, 2024

SharePoint list with calculated column to work out SLA based on days and hours

Dear Team,
 
I am trying to figure out how to calculate SLAs based on the data captured in the SharePoint list. I have a basic understanding of the lists.
 
I have captured the SLA numbers in SLA column where I used 0.5 to show 4 hour SLA.
 
Details:
  • SLA = 4 hours, 1-2 days, 2-5 days
  • Expected Completion Date = =[Date Received]+SLA+ROUNDDOWN(SLA/5,0)*2+IF(WEEKDAY([Date Received])+MOD(SLA,5)>=7,2,0)-ROUNDDOWN(WEEKDAY([Date Received])/7,0)+IF(AND(MOD(SLA,5)=0,WEEKDAY([Date Received])=1),-2,0)+IF(AND(MOD(SLA,5)=0,WEEKDAY([Date Received])=7),-2,0)
  • Result = =IF([Date Completed]="","",IF([Expected Completion Date]>[Date Completed],"Pass","Fail"))
Issue:
  1. The problem is that I am not able to add 4 hours to the "Date Received" and not able to populate correct date under "Expected Completion Date"
  2.  Also, not able to include public holidays in the calculation. Is there a way to include public holidays while calculating the "Expected Completion Date"?
 

Your assistance is greatly appreciated.

 

Regards

Lists
SharePoint Online

2 Replies

  • Rob_Elliott's avatar
    Rob_Elliott
    Silver Contributor
    Jul 24, 2024

    Akshadaks SharePoint has no way of knowing what public holiday dates are, so you would need to build something different using a flow in Power Automate to do your calculations and lookup public holidays.

     

    Rob
    Los Gallardos
    Microsoft Power Automate Community Super User.
    Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)

    • Akshadaks's avatar
      Akshadaks
      Copper Contributor
      Feb 20, 2025

      I have user a workaround for that.

      Created another column for adding the public holidays manually and that gets added to the SLA calculation.

      There are still issues with it though. E.g. if the request is received outside business hours, the SLA should actually start from the next day's business hour and not based on the creation date/time.

      I wonder if there is a way to keep another list with business hours and Power Automate to check if the request received is within that range, if yes, then blank, if no, then to consider the next business day and starting hour for calculation.

Resources