Round up dates to a date ending quarter

[Challis]

Hi,
I am looking to round up dates from column A to a date ending quarter in column B. I have 100s of dates to convert so looking for a formula to put in column B
Example
A B
[TABLE="width: 154"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]Jul-16[/TD]
[TD="align: right"]30/09/2016[/TD]
[/TR]
[TR]
[TD="align: right"]Aug-16[/TD]
[TD="align: right"]30/09/2016[/TD]
[/TR]
[TR]
[TD="align: right"]Sep-16[/TD]
[TD="align: right"]30/09/2016[/TD]
[/TR]
[TR]
[TD="align: right"]Oct-16[/TD]
[TD="align: right"]31/12/2016[/TD]
[/TR]
[TR]
[TD="align: right"]Nov-16[/TD]
[TD="align: right"]31/12/2016[/TD]
[/TR]
[TR]
[TD="align: right"]Dec-16[/TD]
[TD="align: right"]31/12/2016[/TD]
[/TR]
[TR]
[TD="align: right"]Jan-17[/TD]
[TD="align: right"]31/03/2017[/TD]
[/TR]
[TR]
[TD="align: right"]Feb-17[/TD]
[TD="align: right"]31/03/2017[/TD]
[/TR]
[TR]
[TD="align: right"]Mar-17[/TD]
[TD="align: right"]31/03/2017[/TD]
[/TR]
</tbody>[/TABLE]

Help appreciated!

 

[MARK858]

Bound to be a better way when one of the formula Guru's looks at this but the below gives the results you are looking for in your examples.

Excel Workbook
	A	B
1	Jul-16	30/09/2016
2	Aug-16	30/09/2016
3	Sep-16	30/09/2016
4	Oct-16	31/12/2016
5	Nov-16	31/12/2016
6	Dec-16	31/12/2016
7	Jan-17	31/03/2017
8	Feb-17	31/03/2017
9	Mar-17	31/03/2017
10	May-17	30/06/2017
Sheet2

 

[Dave Patton]

Excel 2010
	A	B
1	1-Jul-16	30-Sep-16
1a

Cell Formulas
Range	Formula
B1	=DATE(YEAR(A1),ROUNDUP(MONTH(A1)/3,0)*3+1,0)

 

[jtakw]

Hi,

This would also work:

Book1
	A	B
1	Jul-16	9/30/2016
2	Aug-16	9/30/2016
3	Sep-16	9/30/2016
4	Oct-16	12/31/2016
5	Nov-16	12/31/2016
6	Dec-16	12/31/2016
7	Jan-17	3/31/2017
8	Feb-17	3/31/2017
9	Mar-17	3/31/2017
10	Apr-17	6/30/2017
Sheet452

Cell Formulas
Range	Formula
B1	=EOMONTH(A1,MOD(MONTH(A1)*2,3))

 

[Tetra201]

A tad shorter

=EOMONTH(A1,MOD(-MONTH(A1),3))