Forum Discussion

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

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

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

anonymous9376
Brass Contributor
May 12, 2022
RobElliott I got this error, 'The formula contains a syntax error or is not supported.' I have set the data type to be returned as date only.
- RobElliott
  Silver Contributor
  May 12, 2022
  anonymous9376 just from the error message there is no way to know what the problem is. Did you change the formula at all or paste it in EXACTLY as I posted it?
  
  Rob
  Los Gallardos
  Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)
  - anonymous9376
    Brass Contributor
    May 12, 2022
    RobElliott I pasted your formula but I changed the EndDate because my column name is To Date