+ Reply to Thread
Results 1 to 10 of 10

Previous month's last business day

  1. #1
    Registered User
    Join Date
    06-16-2010
    Location
    Minnetonka, MN
    MS-Off Ver
    Excel 2010
    Posts
    43

    Previous month's last business day

    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.

  2. #2
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Previous month's last business day

    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

  3. #3
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Previous month's last business day

    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.

  4. #4
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Previous month's last business day

    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.

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Previous month's last business day

    Quote Originally Posted by ron2k_1 View Post
    =WORKDAY(EOMONTH(TODAY(),-1)-7,5,A1:A10)
    Why -7 then +5 Ron?

    If you do it that way then you may get the wrong result. What does that formula return if 28th and 29th October 2010 are holidays?

    I suggest

    =WORKDAY(EOMONTH(TODAY(),-1)+1,-1,A1:A10)
    Audere est facere

  6. #6
    Registered User
    Join Date
    06-16-2010
    Location
    Minnetonka, MN
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Previous month's last business day

    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!

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Previous month's last business day

    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

  8. #8
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Previous month's last business day

    Quote Originally Posted by vancoservices View Post
    ....but since when have October 28,29 been holidays?
    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

  9. #9
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Previous month's last business day

    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!

  10. #10
    Registered User
    Join Date
    06-16-2010
    Location
    Minnetonka, MN
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Previous month's last business day

    Ah I see, Brilliant! Great work guys!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1