I'm trying to calculate the last business day of the previous month. I know EOMONTH can get me the last day of the previous month but how would I get the last business day? Any help would be greatly appreciated. Thanks
I'm trying to calculate the last business day of the previous month. I know EOMONTH can get me the last day of the previous month but how would I get the last business day? Any help would be greatly appreciated. Thanks
Last edited by vancoservices; 11-19-2010 at 11:11 AM.
How does this work?
=WORKDAY(EOMONTH(TODAY(),-1)-7,5)
Ron
Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad
Kindly
[1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
[2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
[3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated
Please note that if you have holidays in that month, my above formula is ignoring them. List your holidays (as dates), say, on A1:A10, and then you can add ",A1:A10" (without quotes) to the formula above:
=WORKDAY(EOMONTH(TODAY(),-1)-7,5,A1:A10)
EDIT: Oh, I almost forgot, please note that this formula is volatile. Next month in December, the formula will return November's last working day instead of October's. This seem you already know - you need to activate the analysis pack in the tools menu, and all those who will use the file need to do the same.
Last edited by ron2k_1; 11-18-2010 at 06:14 PM.
In 2007 and 2010 you don't need to run the Analysis Toolpak, those functions are included by default. Only in 2003 and earlier do you need to run that add-in.
Ron/Daddy,
Thanks for the help, both formulas work. I understand the -7,5 , interesting way of doing it. Daddy, maybe I'm having a senior moment but since when have October 28,29 been holidays? Anyway, thanks for the help guys, much appreciated!
DLL's formula is similar, but simpler in that it simply answers the question, what is the last workday prior to the first of the current month?
Entia non sunt multiplicanda sine necessitate
That was just an example - I chose October because the current month is November and you wanted to look at the previous month, but my general point holds.
If your holiday range [A1:A10] included, say, Monday 31st May 2010, Memorial Day in the US, then if you used this formula any time in June 2010
=WORKDAY(EOMONTH(TODAY(),-1)-7,5,A1:A10)
then the result is 1st June 2010, which is not the last working day of the month
DLL's correct! I'll be more careful next time and test the formula before I post them.
Well spotted. I didn't even noticed the version of the OP's excel!
Thanks for coming to the rescue!
Ah I see, Brilliant! Great work guys!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks