Calculate expected arrival date excluding Bank Holidays

[gsrai31]

Hi,
I need help in calculating an expected arrival date excluding Bank Holidays.

I have 3 tables in MS Access database,

1. Table “Standards” lists origin, destination, schedule depart day and expected arrival day.
2. Table “Bank Holidays” lists name of the country and bank holiday dates.
3. Table “Data” lists Origin, Destination and Actual Depart Date. In this table I also have a field named “Expected Arrive Date” that I need to workout as explained below.

“Expected Arrive Date” needs to be calculated based on the standards provided in the “Standards” table for each destination. If a number of Bank holidays fall between the depart and the arrive dates then these number of days need to be added to arrive date. On adding these bank holidays if “Expected Arrive Date” happens to be a bank holiday in the destination country or that day is not one of the Scheduled arrival day then “Expected Arrive Date” has to be the date of next available expected arrival weekday. Hope I have made this clear, if not please let me know.

I have made up three dummy tables in the attached workbook to make it bit more clear. I have manually calculated expected arrive date in the Data table for couple of records that I want to be able automatically calculate.

Hope someone can help me with this.

Thanks in advance

gsrai