Forum Discussion

Copper Contributor

Sep 15, 2023

Solved

Sharepoint List Business Days auto calculation

Hi, I'm trying to find a solution that would enable me to calculate an expected completion date in sharepoint list using different sla days, that also takes into account business days only - Mon ...

Lists

SharePoint Online

Rob_Elliott
Sep 15, 2023
Kidbaybut the following formula will just take into account Monday to Friday:

=[Accepted Date]+SLA+ROUNDDOWN(SLA/5,0)*2+IF(WEEKDAY([Accepted Date])+MOD(SLA,5)>=7,2,0)-ROUNDDOWN(WEEKDAY([Accepted Date])/7,0)+IF(AND(MOD(SLA,5)=0,WEEKDAY([Accepted Date])=1),-2,0)+IF(AND(MOD(SLA,5)=0,WEEKDAY([Accepted Date])=7),-2,0)

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

Rob_Elliott

Silver Contributor

Sep 15, 2023

Kidbaybut the following formula will just take into account Monday to Friday:

=[Accepted Date]+SLA+ROUNDDOWN(SLA/5,0)*2+IF(WEEKDAY([Accepted Date])+MOD(SLA,5)>=7,2,0)-ROUNDDOWN(WEEKDAY([Accepted Date])/7,0)+IF(AND(MOD(SLA,5)=0,WEEKDAY([Accepted Date])=1),-2,0)+IF(AND(MOD(SLA,5)=0,WEEKDAY([Accepted Date])=7),-2,0)

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