SOLVED

# Sharepoint List Business Days auto calculation

Copper Contributor

# 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

best response confirmed by Kidbaybut (Copper Contributor)
Solution

# Re: Sharepoint List Business Days auto calculation

@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)

1 best response

Accepted Solutions
best response confirmed by Kidbaybut (Copper Contributor)
Solution

# Re: Sharepoint List Business Days auto calculation

@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)