Formua to exclude weekend & Public holidays

[howard]

I have a date in D2 and have a formula to compute the due date at the end of the following month less one days. For eg if date in D2 is 31/07/2015 (dd/mm/yyy) , the funds to be paid 30/08/2015

 ="Funds to to be paid "&TEXT(EOMONTH(D2,1)-1,"dd/mm/yyyy")&""

I would like to amend my formula to exclude weekends and public holidays


It would be appreciated if someone could kindly assist me

 

[tybaltlives]

So…this is actually a bit tricky. The main issue is the holidays - there isn't an Excel function that detects if a day is a holiday or not. Holidays are also variable by country and in the US by state. You would need to make a table of holidays and then use something like VLOOKUP to detect if the due date is on a holiday or not. And of course you would need to keep that holiday table updated. So what I have done is just address the weekend issue. If the due date falls on a saturday or sunday, the following formula will adjust the displayed date to be the very next Monday. Note that this could fall into the beginning part of the next month. However, if you need the formula to pick the prior friday it is easily adjusted and then all dates will fall in the desired month. Anyway, this should get you going. It uses the WEEKDAY function set to Saturdays=6 and Sundays=7.

=IF( WEEKDAY((EOMONTH(D2,1)-1),2)<6, "Funds to to be paid "&TEXT(EOMONTH(D2,1)-1,"dd/mm/yyyy")&"",IF( WEEKDAY((EOMONTH(D2,1)-1),2)=6, "Funds to to be paid "&TEXT(EOMONTH(D2,1)-1+2,"dd/mm/yyyy")&"","Funds to to be paid "&TEXT(EOMONTH(D2,1)-1+1,"dd/mm/yyyy")&"" )

 

[MarcelBeug]

Use the workday function:

="Funds to to be paid "&TEXT(WORKDAY(EOMONTH(D2,1)),-1,[I]Holidays[/I]),"dd/mm/yyyy")&""

Holidays refers to a range with dates of holidays you need to type in yourself.

I hope I didn't make a mistake just typing the code out of my head.

Edit: if the last day of the month is a weekend or holdiay, you will get the last working day of that month.

 

[howard]

Thanks for your help, much appreciated

Formula works 100%

What does the , before -1 mean in this formula? ..... EOMONTH(D2,1)),-1 ....

 

[MarcelBeug]

The -1 is the 2nd argument of WORKDAY, meaning that 1 workday must be subtracted from the date in the 1st parameter.

 

[howard]

Thanks for the explanation

 

[MarcelBeug]

You're welcome.