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[TABLE="width: 500"]
<tbody>[TR]
[TD]29/12/16
[/TD]
[TD]N
[/TD]
[/TR]
[TR]
[TD]30/12/16
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]2/1/17
[/TD]
[TD]N
[/TD]
[/TR]
[TR]
[TD]3/1/17
[/TD]
[TD]N
[/TD]
[/TR]
[TR]
[TD]4/1/17
[/TD]
[TD]N
[/TD]
[/TR]
</tbody>[/TABLE]

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