Display last working day of month, taking into account national holidays?

Hello again

So as the title suggests I need a cell to display the last working day of the month, I've already been using the formula below to do just that but it doesn't take into account bank holiday/national holidays.


N1 = Start of the month


=DATE(YEAR(N1),MONTH(N1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(N1),MONTH(N1)+1,0),2)-5))


How would I go about adapting that to include bank/national holidays? I've been searching the web and get the impression I need to use the networkday or workday function but can't get anything to work.


Any help is greatly appreciated



Will.

Do you have a list of bank holidays somewhere?

Pete

perhaps like this:

A1= is your month
C2:C10 = list of holidays

=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,1),-1,$C$2:$C$10))

No but I gather I will be needing that to make it work.


will.

Originally Posted by Tony Valko

Try this...

N1 = some date

G1:G10 = holiday dates

=WORKDAY(EOMONTH(N1,0)+1,-1,G1:G10)

Format as Date

That worked like a charm, thanks Tony


And thanks to Azumi also.