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

Jul-16	30/09/2016
Aug-16	30/09/2016
Sep-16	30/09/2016
Oct-16	31/12/2016
Nov-16	31/12/2016
Dec-16	31/12/2016
Jan-17	31/03/2017
Feb-17	31/03/2017
Mar-17	31/03/2017

<colgroup><col><col></colgroup><tbody>
</tbody>

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))