Sharepoint List column

[anonymous9376]

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

Thank you.

[#2]

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

[#3]

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

[#4]

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

[#5]

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

[#6 · Accepted solution]

@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

[#7]

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

[#8]

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

[#9]

@RobElliott I pasted your formula but I changed the EndDate because my column name is To Date

[#10]

@anonymous9376 Check the internal name of the column from the url bar in list settings and use that. You will most likely find that internal name is something like To_x0020_Date which you should use in your formula. It's one of the reasons I never use spaces in column names.

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

[#11]

@RobElliott I checked the internal name of To Date and its internal name is Field=ToDate then I changed accordingly but still receive an error stating that 'The formula refers to a column that does not exist. '

[#12]

@anonymous9376 without seeing screenshots of your calculated column and the formula I can't help. have you tried putting the column name To Date inside square brackets? Try that also with [ToDate]

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

[#13]

@RobElliott the calculation for that column sometimes returns the wrong date. How to fix it?