Share via

Finding the LAST business day of the month; Finding the LAST business day of the week?

Anonymous
2018-05-24T21:39:41+00:00

Excel 2016/365 with lots of daily data that's date related.  Most of the businesses I work with have data Monday-Friday so I'm wanting a way to determine the last business day of the month (January=31 days; February=28 days except leap years; March=31 days etc.)

FWIW, it's US dollar denominated sales data and each day consists of sales for that day and, if by product, the average price sold with the quantity.

So, let's assume the data is something like this with the data listed in a Date column and then dollar amounts in the Dollar column etc.  Once I find the correct date or day of week, then I'd need the data from the same row relating to whatever I need to do.  Data is generally grouped over several years - maybe 5-10-15 or more rather than have a specific year then month in a tab etc.

   Date            Dollar

01/01/2018    500.00

..... (all of January 2018 of open business days... sometimes Sunday is included sometimes not)

01/29/2018  1000.00

01/30/2018    350.00

01/31/2018    250.00

02/01/2018    150.00

.... all of Feb '18

02/27/2018    275.00

02/28/2018    615.00

03/02/2018    735.00

.... all of Mar '18

03/30/2018    330.00

no 03/31/2018 because 03/31/2018 is on Saturday.  I need the last business day of each month.  Hence, if no data (the rows) then the business was not open.

  1. How can I determine the last business day of each month and use it's sales etc.?
  2. How can I determine the last business day of each week and use it's sales etc.?  Keep in mind that some businesses won't be open for Christmas and other religious or local holidays.  So, say if Christmas falls on Friday (the New Year's also) then Thursday becomes the last business day of the week.

I know I could do a set of conditional IF-THEN for the day of week=6 then 5 etc.

Thanks!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

10 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2018-05-27T01:44:37+00:00

    Hi,

    In cell G2, I entered the following array formula (Ctrl+Shift+Enter) and copied down

    =IF(WEEKDAY(MAX(IF($A$2:$A$49<=EOMONTH(1*("1/"&F2&"/"&E2),0),$A$2:$A$49)),2)<=5,MAX(IF($A$2:$A$49<=EOMONTH(1*("1/"&F2&"/"&E2),0),$A$2:$A$49)),MAX(IF($A$2:$A$49<=MAX(IF($A$2:$A$49<=EOMONTH(1*("1/"&F2&"/"&E2),0),$A$2:$A$49)),$A$2:$A$49)))

    Hope this helps.

    0 comments
  2. Anonymous
    2018-05-26T10:22:22+00:00

    Try this... the BOLD dates are the last days of the month for which the company was open and obtained sales data.  Numbers aren't real.  Dates are!  ;-)  I realize that about 71.4% of the time the last day of the month will be on a M-F or even M-S is 85.7% but it's the complement of this that I need to be aware of.  I just conveniently picked some sample months per your request.

    1/25/2000 287.50
    1/26/2000 284.50
    1/27/2000 285.90
    1/28/2000 282.75
    1/31/2000 282.75
    4/20/2000 279.30
    4/24/2000 279.20
    4/25/2000 287.50
    4/26/2000 275.40
    4/27/2000 276.80
    4/28/2000 272.60
    7/21/2000 280.10
    7/24/2000 279.65
    7/25/2000 278.80
    7/26/2000 279.40
    7/27/2000 278.50
    7/28/2000 277.45
    7/31/2000 276.75
    9/22/2000 271.65
    9/25/2000 274.00
    9/26/2000 274.10
    9/27/2000 278.00
    9/28/2000 275.15
    9/29/2000 274.00
    12/20/2000 273.30
    12/21/2000 274.00
    12/22/2000 273.70
    12/26/2000 274.20
    12/27/2000 275.30
    12/28/2000 272.30
    12/29/2000 272.00
    1/25/2001 264.70
    1/26/2001 262.55
    1/29/2001 262.90
    1/30/2001 265.75
    1/31/2001 265.60
    12/22/2016 128.01
    12/23/2016 133.57
    12/26/2016 132.72
    12/27/2016 138.49
    12/28/2016 141.03
    12/29/2016 157.67
    12/30/2016 150.90
    3/26/2018 353.15
    3/27/2018 344.81
    3/28/2018 324.87
    3/29/2018 325.24
    3/30/2018 325.03
    0 comments
  3. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2018-05-25T23:15:45+00:00

    Hi,

    I'm still not clear.  Please illustrate with an example.  Show the last 5 days of 6 months and clearly show the result.

    0 comments
  4. Anonymous
    2018-05-25T12:18:24+00:00

    Gr8 question.... The date is always the last date of the month in, say, Column A.  That is, if there's a Saturday or Sunday in a month and no data then the date is Friday.  [If there's no data then there's no date.  A date with no sales would still have a date.]  If there's a Saturday with data and it's the 'near' the last day of the month then that's the last day (as long as there isn't any data for future same-month days).

    The issue is finding the last date in the Column A for each month and using the data in that row to review things.  I do have data going past just a single year (more than 2017, 2018 etc....)

    Tx!!

    0 comments
  5. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2018-05-24T23:30:01+00:00

    Hi,

    If the last date of the month is a Saturday or Sunday, and we have sale data for that day, then would that Sat/Sun be the last working day or would it be the previous Friday?

    0 comments