Forum Discussion

Kidbaybut's avatar
Kidbaybut
Copper Contributor
Sep 15, 2023

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

     

     

Resources