Last business day/ weekday calendar quarter

[ShamWD]

Hi, I am trying to create a function in excel to go "Y" when a date is the last weekday in a calendar quarter and "N" if otherwise, but I am failing miserably . In fact I have no idea how to go about it. I had a quick seach through here and didn't find anything.


So column A is just a list of dates and column B needs to be a formula that says Y this is the last business day in the calendar quarter or N if not. I''ve used the example of December 2016 where 31 December is a Saturday, so the formula needs to say yes to Dec 30 2016 as being the last weekday/ business day in the calendar quarter? I've left out Dec 31 2016 and January 1 2017 from the below due to these being a saturday and a bank holiday respectively

Can somebody help please?

Date LBDCQ

29/12/16	N
30/12/16	Y
2/1/17	N
3/1/17	N
4/1/17	N

<tbody>
</tbody>

thanks!

 

[Derek Brown]

Hello and welcome to The Board.
As an example, for a date in A1, you could use something like the following:
=WORKDAY(DATE(YEAR(A1),(ROUNDUP(MONTH(A1)/3,0)*3)+1,1),-1)
This gives the last weekday in the Quarter. You then just need to adjust that formula to test against your dates.
Useful references for dates:
Worksheet Functions For Dates And Times
Find last day of any month with this simple trick [formulas] | Chandoo.org - Learn Microsoft Excel Online

 

[ShamWD]

Thanks a million Derek, that's fantastic, just added an if formula and it works great.
Thanks for the help