First day of Last month of the Calendar Quarter

[jwb1012]

Hello, I need a formula for the first day of the last month of the quarter... Any thoughts on this?

Value = 1/31/2018... Result = 03/01/2018

Value = 3/28/2018... Result = 03/01/2018

Value = 04/01/2018... Result = 06/01/2018

Value = 05/01/2018... Result = 06/01/2018​

 

[Dave Patton]

Excel 2010
	M	N	O	P	Q
1	31-Jan-18	01-03-18		1	2
2	2-Feb-18	01-03-18		2	1
3	31-Mar-18	01-03-18		3	0
4	1-Apr-18	01-06-18		4	2
5	1-May-18	01-06-18		5	1
6	15-Jun-18	01-06-18		6	0
4a

Cell Formulas
Range	Formula
N1	=DATE(YEAR(M1),MONTH(M1)+LOOKUP(MONTH(M1),P1:Q6),1)
N2	=DATE(YEAR(M2),MONTH(M2)+LOOKUP(MONTH(M2),P2:Q7),1)
N3	=DATE(YEAR(M3),MONTH(M3)+LOOKUP(MONTH(M3),P3:Q8),1)
N4	=DATE(YEAR(M4),MONTH(M4)+LOOKUP(MONTH(M4),P4:Q9),1)
N5	=DATE(YEAR(M5),MONTH(M5)+LOOKUP(MONTH(M5),P5:Q10),1)
N6	=DATE(YEAR(M6),MONTH(M6)+LOOKUP(MONTH(M6),P6:Q11),1)
P6	=MONTH(M6)

 

[Takae]

A1=1/31/2018
B1 =DATE(ROUNDUP(MONTH(A1)/3,0)*3,1,YEAR(A1))

please rearrange DATE foumula as your version.
Hope this helps.

 

[jwb1012]

Hmm this doesn't seem to work.... any other thoughts?

[TABLE="width: 214"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD="align: right"]1/31/2018[/TD]
[TD="align: right"]7/10/1908[/TD]
[/TR]
[TR]
[TD="align: right"]3/28/2018[/TD]
[TD="align: right"]7/10/1908[/TD]
[/TR]
[TR]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]7/11/1911[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2018[/TD]
[TD="align: right"]7/11/1911[/TD]
[/TR]
[TR]
[TD="align: right"]12/31/2018[/TD]
[TD="align: right"]7/10/1917[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]7/10/1908[/TD]
[/TR]
</tbody>[/TABLE]

 

[Tetra201]

Try

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