How to exclude holidays from calculated column

Question

Hey I am trying to calculated the difference between two dates excluding weekends and holidays but so fat I was only able to come across this formula

IF(ISERROR(DATEDIF([Start Date], [End Date],"d")), "",(DATEDIF([Start Date], [End Date], "d")) + 1 - INT(DATEDIF([Start Date], [End Date], "d") / 7) * 2 - IF((WEEKDAY([End Date]) - WEEKDAY([Start Date])) < 0, 2, 0) - IF(OR(AND(WEEKDAY([End Date]) = 7, WEEKDAY([Start Date]) = 7), AND(WEEKDAY([End Date]) = 1, WEEKDAY([Start Date]) = 1)), 1, 0) - IF(AND(WEEKDAY([Start Date]) = 1, (WEEKDAY([End Date]) - WEEKDAY([Start Date])) > 0), 1, 0) - IF(AND(NOT(WEEKDAY([Start Date]) = 7), WEEKDAY([End Date]) = 7), 1, 0))

This formula works for weekdays but not holidays is there a way to make it include holidays.

Answer 1

There is no function available in Calculated Column which you can use it to find out Holiday. And also Public Holiday can also vary according to different region or country.

Here you can use Power Automate to calculate date difference excluding weekend Public Holiday as well.

In order to get public holiday details, either you can create custom SharePoint list where you can manage public holiday or you can use any third party API which gives public holiday details.

Following are some useful articles

1. https://www.chorus.co/resources/news/calculating-working-days-in-power-automate-or-logic-apps
2. https://docs.microsoft.com/en-us/connectors/sharepointonline/
3. https://ryanmaclean365.com/2019/12/12/calculate-working-days-in-a-time-period-using-power-automate/

Answer 2

You can't do it in a calculated column, but you can use a child flow that runs when the appropriate property is changed. The flow calculates a new date excluding weekends an holidays. I have created such a flow and recorded the video how to use it https://youtu.be/zPKEGT03AZE