SharePoint list with calculated column to work out SLA based on days and hours
SLA = 4 hours, 1-2 days, 2-5 daysExpected 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”))
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” 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
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 daysExpected 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: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” 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 Read More
