Calculated date difference excluded public holiday and weekend

[thomascheung]

Hi, everyone, i am trying to use calculated type column to calculate the date difference. 

=NETWORKDAYS(start date, end date, (List of public holiday)).

The public dates are saved in sharepoint list.

Is that possible to extract the data (public date) from other sharepoint list for calculation (networkdays). 

[#2]

@thomascheung No, this is a known limitation of SharePoint calculated columns.

Calculated column formula can only reference the column values from same list item/row. It cannot access column values (data) from other lists in SharePoint site.

Even if you use lookup columns, those are not supported in calculated column formula.

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

[#3]

Hi @thomascheung,

As @ganeshsanap said: Not with a calculated column...

But you could create a number field "DaysBetween" column and create a PowerAutomate flow that

• Is triggered by item update
• Checks if the "DaysBetween" column is empty (to avoid an endless loop)
• Caclulates the date difference in days between StartDate and Enddate
• Queries how many public holidays are in your "Holiday list" between StartDate and Enddate
• Subtracts both values
• Updates the item and set the "DaysBetween" column to the caluclated value

Best Regards,
Sven

[#4]

Hi @SvenSieverding 

Can you guide on the PowerAutomate flow to update the field 'DaysBetween'.