Forum Discussion

anonymous9376's avatar
anonymous9376
Brass Contributor
May 11, 2022
Solved

Sharepoint List column

Hi, how do I create a calculated column based on the End Date of a leave that includes working days only? What's the formula for it? (It should auto calculate a date based on weekdays and company hol...
SharePoint Online
  • RobElliott's avatar
    RobElliott
    May 12, 2022

    anonymous9376 well that's a completely different formula. Set the data type to be returned as date only:

    =EndDate+1+ROUNDDOWN(3/5,0)*2+IF(WEEKDAY(EndDate)+MOD(3,5)>=7,2,0)-ROUNDDOWN(WEEKDAY(EndDate)/7,0)+IF(AND(MOD(3,5)=0,WEEKDAY(EndDate)=1),-2,0)+IF(AND(MOD(3,5)=0,WEEKDAY(EndDate)=7),-2,0)

     

     

    Rob
    Los Gallardos
    Microsoft Power Automate Community Super User

RobElliott's avatar
RobElliott
Silver Contributor
May 11, 2022

anonymous9376 you can do a formula for calculating the number of working days between the start date and end date as follows:

=(DATEDIF([StartDate],[EndDate],"D"))-INT(DATEDIF([StartDate],[EndDate],"D")/7)*2-IF(WEEKDAY([EndDate])<WEEKDAY([StartDate]),2,IF(OR(WEEKDAY([EndDate])=7,WEEKDAY([StartDate])=1),1,0))+1

 

But that won't/can't include company holidays so you'd think of some other method to take those into account.

 

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)