Seeking Formula to exclude Weekends

Good Morning,

I am seeking to amend the formula found under column "N2" of the attached Mock Up sheet to include only Working days (Monday to Friday) and also exclude Public Holidays listed under the sheet "Public Holidays".

Thanks for your help!!

Regards,

M

Hi Ali,

That is almost perfect, Thank you! I've only noticed the following issue:

For e.g. as shown below, the breach date should fall on the 17th of April, on the 5th working day (I've changed all SLAs to 5 working days to make it easier to follow), however the breach date is falling on the 20th instead


MockUpExcel.PNG

Hi Ali,

My apologies...I thought that part of the formula could be altered without it having an effect on the Due Date Calculation, as it would always exclude Public Holidays and Weekends. Basically, the Due Date will be moved sometime in the near future according to latest regulatory requirements which is why I was testing this out.

Hi Ali,

I've attached the excel sheet for your perusal

Try:

=WORKDAY.INTL(H2-1,CHOOSE(MATCH($D2,{"MB","LE","SUMO"},0),5,5,5),1,PublicHolidays!$B$2:$B$15)

Hi Phuocam,

This works perfectly fine on normal Excel, however I am receiving a #VALUE! Error when I am trying to transfer the formula to Excel Online

Attachment 670872

Is there any work around for this ?

Thank you!