Remove specific hours and weekends (and holidays, if it's possible)

Question

Hi All,

I have scenario which was partially mentioned in another topics but it is more complex. I have a list of 'booking end times' and I want to add to them specified value (e.g. 2 hours). The problem is that adding time is intended to apply to specific periods (Monday-Friday 8am-4pm), so I'd like to exclude specified periods (Monday-Friday 4pm-8am, weekends and holidays, if possible). Below are examples:

Booking end time -> Expected value

26/10/2023 11:00 -> 26/10/2023 13:00
20/10/2023 (friday) 15:45 -> 23/10/2023 9:45 (monday)

23/10/2023 15:00 -> 24/10/2023 9:00

Thank you for your help

AncientPandaman · Accepted Answer

similar, but I add something to skip the holiday.

Idea is using find & replace to replace the holiday with next available day.

edit: to avoid the holiday is skip in middle, like Friday and Monday is holiday.

calculate the next day first only use multiple rows to replace the next available day.

gglodowicz · Accepted Answer

Thank you for your help. Much appreciated.