Forum Discussion
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 holidays)
Thank you.
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
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))+1But 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:
Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)
