SOLVED

Sharepoint List column

%3CLINGO-SUB%20id%3D%22lingo-sub-3355942%22%20slang%3D%22en-US%22%3ESharepoint%20List%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3355942%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20how%20do%20I%20create%20a%20calculated%20column%20based%20on%20the%20End%20Date%20of%20a%20leave%20that%20includes%20working%20days%20only%3F%20What's%20the%20formula%20for%20it%3F%20(It%20should%20auto%20calculate%20a%20date%20based%20on%20weekdays%20and%20company%20holidays)%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3355942%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3356465%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20List%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3356465%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1364262%22%20target%3D%22_blank%22%3E%40anonymous9376%3C%2FA%3E%26nbsp%3Byou%20can%20do%20a%20formula%20for%20calculating%20the%20number%20of%20working%20days%20between%20the%20start%20date%20and%20end%20date%20as%20follows%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3D(DATEDIF(%5BStartDate%5D%2C%5BEndDate%5D%2C%22D%22))-INT(DATEDIF(%5BStartDate%5D%2C%5BEndDate%5D%2C%22D%22)%2F7)*2-IF(WEEKDAY(%5BEndDate%5D)%3CWEEKDAY%3E%3C%2FWEEKDAY%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20that%20won't%2Fcan't%20include%20company%20holidays%20so%20you'd%20think%20of%20some%20other%20method%20to%20take%20those%20into%20account.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EIntranet%2C%20SharePoint%20and%20Power%20Platform%20Manager%20(and%20classic%201967%20Morris%20Traveller%20driver)%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3361811%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20List%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3361811%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40RobElliott%3C%2FA%3EI%20input%20the%20formula%20you%20wrote%20but%20it%20returns%2012%2F31%2F1899.%20That's%20the%20default%20for%20the%20WEEKDAY%20function%2C%20isn't%20it%3F%20How%20to%20fix%20it%3F%3CBR%20%2F%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3363242%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20List%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3363242%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1364262%22%20target%3D%22_blank%22%3E%40anonymous9376%3C%2FA%3E%26nbsp%3Byou%20must%20make%20sure%20that%20in%20the%20list%20settings%2C%20for%20this%20calculated%20column%20you%20set%20the%20data%20type%20to%20be%20returned%20as%20a%20number%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22CalcColumnListSettings.png%22%20style%3D%22width%3A%20534px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371113iE247AA1615E0670A%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22CalcColumnListSettings.png%22%20alt%3D%22CalcColumnListSettings.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22WorkingDays2.png%22%20style%3D%22width%3A%20921px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371119i092D3C8D3653650B%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22WorkingDays2.png%22%20alt%3D%22WorkingDays2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EIntranet%2C%20SharePoint%20and%20Power%20Platform%20Manager%20(and%20classic%201967%20Morris%20Traveller%20driver)%3C%2FFONT%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3363260%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20List%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3363260%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40RobElliott%3C%2FA%3E%20Thank%20you%20for%20that.%20But%20I'm%20sorry%20that%20I%20forgot%20to%20mention%20that%20I%20want%20a%20Resume%20Work%20Date%20column%20that%20is%20a%20day%20after%20the%20End%20Date%20but%20only%20includes%20working%20days.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3363340%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20List%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3363340%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1364262%22%20target%3D%22_blank%22%3E%40anonymous9376%3C%2FA%3E%26nbsp%3Bwell%20that's%20a%20completely%20different%20formula.%20Set%20the%20data%20type%20to%20be%20returned%20as%20date%20only%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DEndDate%2B1%2BROUNDDOWN(3%2F5%2C0)*2%2BIF(WEEKDAY(EndDate)%2BMOD(3%2C5)%26gt%3B%3D7%2C2%2C0)-ROUNDDOWN(WEEKDAY(EndDate)%2F7%2C0)%2BIF(AND(MOD(3%2C5)%3D0%2CWEEKDAY(EndDate)%3D1)%2C-2%2C0)%2BIF(AND(MOD(3%2C5)%3D0%2CWEEKDAY(EndDate)%3D7)%2C-2%2C0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22ReturnToWork.png%22%20style%3D%22width%3A%20646px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371121i7C8ACF403BBFAB08%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22ReturnToWork.png%22%20alt%3D%22ReturnToWork.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3363451%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20List%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3363451%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40RobElliott%3C%2FA%3E%20I%20got%20this%20error%2C%20'The%20formula%20contains%20a%20syntax%20error%20or%20is%20not%20supported.'%20I%20have%20set%20the%20data%20type%20to%20be%20returned%20as%20date%20only.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3363487%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20List%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3363487%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1364262%22%20target%3D%22_blank%22%3E%40anonymous9376%3C%2FA%3E%26nbsp%3Bjust%20from%20the%20error%20message%20there%20is%20no%20way%20to%20know%20what%20the%20problem%20is.%20Did%20you%20change%20the%20formula%20at%20all%20or%20paste%20it%20in%20EXACTLY%20as%20I%20posted%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EIntranet%2C%20SharePoint%20and%20Power%20Platform%20Manager%20(and%20classic%201967%20Morris%20Traveller%20driver)%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3363492%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20List%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3363492%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40RobElliott%3C%2FA%3E%20I%20pasted%20your%20formula%20but%20I%20changed%20the%20EndDate%20because%20my%20column%20name%20is%20To%20Date%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3363519%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20List%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3363519%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1364262%22%20target%3D%22_blank%22%3E%40anonymous9376%3C%2FA%3E%26nbsp%3BCheck%20the%20internal%20name%20of%20the%20column%20from%20the%20url%20bar%20in%20list%20settings%20and%20use%20that.%20You%20will%20most%20likely%20find%20that%20internal%20name%20is%20something%20like%26nbsp%3BTo_x0020_Date%20which%20you%20should%20use%20in%20your%20formula.%20It's%20one%20of%20the%20reasons%20I%20never%20use%20spaces%20in%20column%20names.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EIntranet%2C%20SharePoint%20and%20Power%20Platform%20Manager%20(and%20classic%201967%20Morris%20Traveller%20driver)%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3363622%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20List%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3363622%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40RobElliott%3C%2FA%3E%20I%20checked%20the%20internal%20name%20of%20To%20Date%20and%20its%20internal%20name%20is%20Field%3DToDate%20then%20I%20changed%20accordingly%20but%20still%20receive%20an%20error%20stating%20that%20'The%20formula%20refers%20to%20a%20column%20that%20does%20not%20exist.%20'%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3363627%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20List%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3363627%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1364262%22%20target%3D%22_blank%22%3E%40anonymous9376%3C%2FA%3E%26nbsp%3Bwithout%20seeing%20screenshots%20of%20your%20calculated%20column%20and%20the%20formula%20I%20can't%20help.%20have%20you%20tried%20putting%20the%20column%20name%20To%20Date%20inside%20square%20brackets%3F%20Try%20that%20also%20with%20%5BToDate%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EIntranet%2C%20SharePoint%20and%20Power%20Platform%20Manager%20(and%20classic%201967%20Morris%20Traveller%20driver)%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3368089%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20List%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3368089%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40RobElliott%3C%2FA%3E%20It's%20working%20now%2C%20thank%20you!%20How%20to%20exclude%20the%20Holidays%3F%20Integrating%20it%20with%20Power%20Apps%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3397795%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20List%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3397795%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40RobElliott%3C%2FA%3E%20the%20calculation%20for%20that%20column%20sometimes%20returns%20the%20wrong%20date.%20How%20to%20fix%20it%3F%3C%2FLINGO-BODY%3E
Contributor

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.

12 Replies

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

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

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

CalcColumnListSettings.png

 

WorkingDays2.png

 

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

 

@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.
best response confirmed by anonymous9376 (Contributor)
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)

 

ReturnToWork.png

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

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

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

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

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

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

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

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