Is there a function (or a composition of functions) that
produces the last business day of a month? Ideally
incorporating a calendar for a specific country, but even
something that avoids saturdays and sundays would be good
enough.
Thanks,
giacomo
You can use the WORKDAY and DATE functions:
=WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,1),-1)
Given any date in A1, the DATE function returns the 1st day of the subsequent
month. The WORKDAY function, as written, then subtracts one workday from that.
That excludes Saturday and Sunday. There is an optional argument for the
WORKDAY function to include holidays, and this could be adjusted for different
countries.
One could possibly use the EOMONTH function to compute the first day of the
next month, but I don't know for sure if that will work internationally. VBA
and the ATP are sometimes US centric with regard to dates.
Something like:
=WORKDAY(EOMONTH(A1,0)+1,-1) would be a bit simpler. You can check it out.
If the WORKDAY or EOMONTH function is not available, and returns the #NAME?
error, install and load the Analysis ToolPak add-in.
How?
On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.
--ron
