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)

anonymous9376's avatar
anonymous9376
Brass Contributor
May 12, 2022

RobElliottI input the formula you wrote but it returns 12/31/1899. That's the default for the WEEKDAY function, isn't it? How to fix it?
Thank you.

  • RobElliott's avatar
    RobElliott
    Silver Contributor
    May 12, 2022

    anonymous9376 you must make sure that in the list settings, for this calculated column you set the data type to be returned as a number:

     

     

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

     

    • anonymous9376's avatar
      anonymous9376
      Brass Contributor
      May 12, 2022
      RobElliott Thank you for that. But I'm sorry that I forgot to mention that I want a Resume Work Date column that is a day after the End Date but only includes working days.
      • RobElliott's avatar
        RobElliott
        Silver Contributor
        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

Resources