Settlement Calculation if there are consecutive days holiday and weekends

[ShaileshDevadiga]

Hi All.

I am facing issue in regard to settlement date calculating specially when there is consecutive holidays

I get Trade Date from source, so settlement date is calculated as Trade Date +2 days, so to get settlement date with taking weekend ( Saturday and Sunday ) into consideration, I use below formula which works fine
if datetimeformat([Trade Date],'%A')="Friday" then datetimeadd([Trade Date],4,'days') elseif datetimeformat([Trade Date],'%A')="Thursday" then datetimeadd([TradeDate],4,'day') else datetimedd([Trade Date],2,'day') endif

Then to consider holidays, i concanete holiday list and then check settlement date is there in the holiday list, if there then updated settlement date will be +1 days.
But I am struggling when there is consecutive holidays.
For example, below are list of holidays and what I am getting as settlement date.
Trade booked on 1st May, settlement date is 3rd May but since its holiday from 3rd May to 5th May and then Saturday and Sunday, it should capture settlement date as 8th May, same for trade booked on 2nd May, should capture settlement date as 9th May.

Holidays
2023-01-02
2023-01-03
2023-01-09
2023-02-23
2023-03-21
2023-05-03
2023-05-04
2023-05-05
2023-07-17
2023-08-11
2023-09-18
2023-10-09
2023-10-10

Trade Date	Settlement Date	Correct Settlement Date	What I am getting
2023-04-27	2023-05-01	2023-05-01	2023-05-01
2023-04-28	2023-05-02	2023-05-02	2023-05-02
2023-05-01	2023-05-03	2023-05-08	2023-05-04
2023-05-02	2023-05-04	2023-05-09	2023-05-05
2023-05-08	2023-05-10	2023-05-10	2023-05-10
2023-05-09	2023-05-11	2023-05-11	2023-05-11
2023-05-10	2023-05-12	2023-05-12	2023-05-12

[J_Gonz]

@Yoshiro_Fujimori 

I really appreciate your help so far. Your logic works perfectly for every T+1 and T+2 scenario I have however T+0 scenarios are filtered out. If a trade settles T+0 on a business day then Trade Date = Settlement Date if Trade Date is a holiday then it will settle next business day. 

Hope you're still able to help. 

Thank You,

Jessica

[Yoshiro_Fujimori]

For T+0 scenario, I think you just need to change the filter condition as below;

Filter 1

Before [Trade Date] <  [Date]

After  [Trade Date] <= [Date]

Filter 2

Before [RunTot_BusinessDay] = 2

After  [RunTot_BusinessDay] = 1

Result

Trade Date	Date
2023-01-02	2023-01-04
2023-01-03	2023-01-04
2023-01-04	2023-01-04
2023-04-27	2023-04-27
2023-04-28	2023-04-28
2023-05-01	2023-05-01
2023-05-02	2023-05-02
2023-05-03	2023-05-08
2023-05-08	2023-05-08
2023-05-09	2023-05-09
2023-05-10	2023-05-10

[J_Gonz]

@Yoshiro_Fujimori 

That did it :) Thank you SO much!!