Forum Discussion
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 to Fri.
I have a column called "accepted date" (date format) and a column called "SLA" (number format - multiple variables between 2 and 90 days) filled with existing data. I have a third column called "Expected Completion Date" (date format) that is blank. Im trying to understand if its possible to have the SLA Days be added to the accepted date column to then produce a date in the expected column that only counts business days. For example 15/09/2023 + 14 days (taking into account business days) = 04/10/2023
Now I can do the addition using a formula but I can't seem to find one that takes into account mon to Friday only and that produces a date output.
Is this even possible within SP list or should I try another method?
Thanks
KB
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_ElliottBronze Contributor
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)