# most recent month-end, quarter-end and year-end workday, non-holiday dates

1. ## most recent month-end, quarter-end and year-end workday, non-holiday dates

Hi,

Spent most of the morning on this and could not get it to work properly

I have a date in cell A1 which is today's date. In B1:B10 I have a list of holiday dates that have been manually entered.

1/8/2018 =today()
1/15/2018 manual entries
2/19/2018
...
12/25/2018

For the most recent month end, the formula =WORKDAY(DATE(YEAR(A1),MONTH(A1),1),-1,B1:B10) works great!

Problem:
I need the most recent quarter-end and year-end workday and non-holiday dates to display, relative to the date in A1. I also need a 3 year ago, month-end date. Please help.

2. ## Re: most recent month-end, quarter-end and year-end workday, non-holiday dates

I assume that your quarters end at the end of March, June, September and December?

If so try this formula

=WORKDAY(DATE(YEAR(A1),FLOOR(MONTH(A1),3)+1,1),-1,B1:B10)

For most recent month end this is shorter

=WORKDAY(A1-DAY(A1)+1,-1,B1:B10)

For year end

=WORKDAY(DATE(YEAR(A1),1,1),-1,B1:B10)

For 3 year ago month end try this:

=WORKDAY(EDATE(A1-DAY(A1)+1,-36),-1,B1:B10)

3. ## Re: most recent month-end, quarter-end and year-end workday, non-holiday dates

I assume that your quarters end at the end of March, June, September and December?

If so try this formula

=WORKDAY(DATE(YEAR(A1),FLOOR(MONTH(A1),3)+1,1),-1,B1:B10)

For most recent month end this is shorter

=WORKDAY(A1-DAY(A1)+1,-1,B1:B10)

For year end

=WORKDAY(DATE(YEAR(A1),1,1),-1,B1:B10)

For 3 year ago month end try this:

=WORKDAY(EDATE(A1-DAY(A1)+1,-36),-1,B1:B10)
You're the best! Thank you this is very helpful.

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