Reciprocating quarterly date

[Luigi802]

I feel like this should be a simple 1, but I can't figure it out. I'm trying to make a date automatically change every 3 months "quarterly." For example, say =TODAY() "3 Jan 2019", is in A1, I want cell B1 to show 30 Apr 2019 "last day of month", then I want B1 to stay the same until A1 reaches 1 Apr 2019.

 

[vletm]

Luigi802
for B1 =EOMONTH(A1,3)

 

[chirayu]

Formula example

 

[Luigi802]

Luigi802
for B1 =EOMONTH(A1,3)

Well yes that does make it 3 months more, but as soon as A1 changes to February then B1 just changes to March, I would like it to stay in April until A1 hits April

 

[Luigi802]

Formula example

OK, but is there a way to do that all in 1 formula, like without having to define the quarters in other cells?

 

[vletm]

Luigi802
yes
... version2 =EOMONTH(A1-(A1-DATE(2018,1,1)),3)

 

[chirayu]

see example

 

[Chihiro]

Wait... you want quarterly... then should TODAY() not return 3/31/2019? Which is end of Q1... Not 4/30/2019.

If that's the case...
=EOMONTH(DATE(2019,ROUNDUP(MONTH(A2)/3,0)*3,1),0)

If it's 4 month per period, just change 3 used in formula above with 4.

Edit: To make it more robust, you can replace 2019 with, Year(A2).

 

[bosco_yip]

Or, similar to Chihiro's formula but in shorter

=EOMONTH("1/"&-INT(-MONTH(A1)/3)*3,0)

Regards
Bosco

 

[Luigi802]

see example

Once A1 it reaches April, then B1 should be 31 Jul 2019 until A1 reaches Jul, then B1 would be 31 Oct 2019

 

[vletm]

Luigi802
version 3
=EOMONTH(DATE(YEAR(A1),1+3*INT((MONTH(A1)-1)/3),1),3)
LEFT table results >

RIGHT table results
=EOMONTH(DATE(YEAR(A1),3*INT((MONTH(A1)-1)/3),1),3)
... if You would like that January would be the 1st quarter too

 

[Luigi802]

Or, similar to Chihiro's formula but in shorter

=EOMONTH("1/"&-INT(-MONTH(A1)/3)*3,0)

Regards
Bosco

Almost......Once A1 it reaches April, then B1 should be 31 Jul 2019 until A1 reaches Jul, then B1 would be 31 Oct 2019

 

[Luigi802]

Luigi802
version 3
=EOMONTH(DATE(YEAR(A1),1+3*INT((MONTH(A1)-1)/3),1),3)
LEFT table results >
View attachment 57319 View attachment 57320
RIGHT table results
=EOMONTH(DATE(YEAR(A1),3*INT((MONTH(A1)-1)/3),1),3)
... if You would like that January would be the 1st quarter too

AWESOME! You are the man! The "left table" 1 works perfectly!

 

[Peter Bartholomew]

To calculate the quarter-end relative to the current date:

= EOMONTH( date, MOD(12-MONTH(date), 3))

The 12 can be omitted if you are happy with MOD of negative numbers. If 'date' is a multicell range (unlikely but I do things like that) you would need +date to convert it to an array.