Return last working day of a month

[mikeymay]

Is it possible to easily return get the last working day of the month for months where Mon-Fri are not the workdays?

I have a tool where the user will select the workdays (Mon-Fri, Mon-Sat, Tue-Sat, Tue-Sun, etc) and I need to show the last working day of each month also taking into account any bank holidays.


Thanks

 

[Jonmo1]

Which version of Excel are you using?

XL2010 has Workday.INTL which you can specify the working days.
Example

=WORKDAY.INTL(A1,10,"1000001",Holidays)

That string "1000001" represents which days are working days / weekends.
Each number represents a day of the week, starting with MONDAY
1 = Weekend day
0 = Working day

so "1000001" makes Tuesday-Saturday working days

Hope that helps.

 

[mikeymay]

I am using 2013 but I need it to be compatible with 2007 onward if possible

 

[Rick Rothstein]

I am using 2013 but I need it to be compatible with 2007 onward if possible

If you have the Analysis ToolPak add-in loaded in your copies of XL2007 (not needed for higher versions), you can use this formula...

=EOMONTH(A1,0)-MAX(0,WEEKDAY(EOMONTH(A1,0),2)-5)