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
  • Rob_Elliott's avatar
    Rob_Elliott
    Bronze 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)

Resources