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

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

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