Specific Date Formula that Adjusts for Quarter

[jmdiva]

Hi - I'm trying to find a revolving date formula that will calculate what Quarter I am currently in but update itself once that particular date has passed. For instance...
[TABLE="width: 500"]
<tbody>[TR]
[TD]Day[/TD]
[TD]Month[/TD]
[TD]Year[/TD]
[TD]Q[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]5[/TD]
[TD]2018[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Start Date[/TD]
[TD]Next Pay Date[/TD]
[TD]Next Pay Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]9/30/18[/TD]
[TD]12/30/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need a formula that I can link the start date with. I'm starting with June 30 (the last month of Q2) and need a formula that will get me 9/30/18 and 12/30/18 based off the fact that today's date is 5/10/18.

Please help, thanks!

 

[markmzz]

Hi - I'm trying to find a revolving date formula that will calculate what Quarter I am currently in but update itself once that particular date has passed. For instance...
[TABLE="width: 500"]
<tbody>[TR]
[TD]Day[/TD]
[TD]Month[/TD]
[TD]Year[/TD]
[TD]Q[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]5[/TD]
[TD]2018[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Start Date[/TD]
[TD]Next Pay Date[/TD]
[TD]Next Pay Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]9/30/18[/TD]
[TD]12/30/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I need a formula that I can link the start date with. I'm starting with June 30 (the last month of Q2) and need a formula that will get me 9/30/18 and 12/30/18 based off the fact that today's date is 5/10/18.
Please help, thanks!

Hi!

If I understand what you want, maybe the formulas below can helps:

In D2

=INT((B2-1)/3)+1

In A5

=DATE(C2,CEILING(B2,3)+1,0)

In B5 and copy to the right

=EDATE(A5,3)


[TABLE="class: grid, width: 359"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Day[/TD]
[TD="align: center"]Month[/TD]
[TD="align: center"]Year[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]2018[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Start Date[/TD]
[TD="align: center"]Next Pay Date[/TD]
[TD="align: center"]Next Pay Date[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]30/06/2018[/TD]
[TD="align: center"]30/09/2018[/TD]
[TD="align: center"]30/12/2018[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]***[/TD]
[TD="align: center"]**********[/TD]
[TD="align: center"]*************[/TD]
[TD="align: center"]*************[/TD]
[TD="align: center"]****[/TD]
[TD="align: center"]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

 

[jmdiva]

Okay this is really close but say I wanted to keep the updated dates in one cell. So as time passes and we move into Q3 I'd like cell B5 to automatically read 12/30/2018. I laid it out in 2 separate cells but ideally I'd like to make the formula smart enough to update with the passage of time.

 

[markmzz]

Okay this is really close but say I wanted to keep the updated dates in one cell. So as time passes and we move into Q3 I'd like cell B5 to automatically read 12/30/2018. I laid it out in 2 separate cells but ideally I'd like to make the formula smart enough to update with the passage of time.

I didn't understand correctly what you want, but maybe this:


In B5

=EDATE(DATE(C2,CEILING(B2,3)+1,0),3)

Or

=EDATE(DATE(YEAR(TODAY()),CEILING(MONTH(TODAY()),3)+1,0),3)


[TABLE="class: grid, width: 359"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Day[/TD]
[TD="align: center"]Month[/TD]
[TD="align: center"]Year[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]2018[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Next Pay Date[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]30/09/2018[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]***[/TD]
[TD="align: center"]**********[/TD]
[TD="align: center"]*************[/TD]
[TD="align: center"]*************[/TD]
[TD="align: center"]****[/TD]
[TD="align: center"]**[/TD]
[/TR]
</tbody>[/TABLE]


If not, then post two or more examples (with what you want in details).


Markmzz

 

[jmdiva]

[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Day[/TD]
[TD="align: center"]Month[/TD]
[TD="align: center"]Year[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]2018[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Next Pay Date[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]30/09/2018[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]***[/TD]
[TD="align: center"]**********[/TD]
[TD="align: center"]*************[/TD]
[TD="align: center"]*************[/TD]
[TD="align: center"]****[/TD]
[TD="align: center"]**[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5/10/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]day[/TD]
[TD]month[/TD]
[TD]year[/TD]
[TD]Q[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10[/TD]
[TD]5[/TD]
[TD]2018[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]start date
[/TD]
[TD]pay date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6/30/2017[/TD]
[TD]6/30/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1/1/2018[/TD]
[TD]7/1/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]7/1/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]day[/TD]
[TD]month[/TD]
[TD]year[/TD]
[TD]Q[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]2018[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]start date[/TD]
[TD]pay date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6/30/2017[/TD]
[TD]9/30/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1/1/2018[/TD]
[TD]7/1/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I hope this helps - so as you can see today's date in A1 in the first and second table show the passage of time. I want to utilize A1, B1, C1, D1 as well because I'm using these for other formulas in my spreadsheet as well.

The start dates remain fixed because that's when the client signed on. However, their pay date gets updated quarterly from that starting date. So as of 5/10/18, client who signed on 6/30/2017 gets paid 6/30/2018. Same thing goes for the second client. I want the dates in cells B6 & b7 to be smart enough to update the pay date quarterly using (today's year) but these are not calendar Quarters, they start whenever the start date does.

 

[markmzz]

Hi!

Try this:

In B6 and copy down (Sheet1 and Sheet2) - use Ctrl+Shift+Enter to enter the formula

=IF($A$1=A6,A6,MIN(IF($A$1 <= EDATE(A6,(ROW(INDIRECT("1:"&MAX(DATEDIF(A6,$A$1,"m"),1))))*3),
EDATE(A6,(ROW(INDIRECT("1:"&MAX(DATEDIF(A6,$A$1,"m"),1))))*3))))


[TABLE="class: grid, width: 823"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]10/05/2018[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Sheet1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]01/07/2018[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Sheet2[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]day[/TD]
[TD="align: center"]month[/TD]
[TD="align: center"]year[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]day[/TD]
[TD="align: center"]month[/TD]
[TD="align: center"]year[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]2018[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]2018[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]start date[/TD]
[TD="align: center"]pay date[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]start date[/TD]
[TD="align: center"]pay date[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]30/06/2017[/TD]
[TD="align: center"]30/06/2018[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]30/06/2017[/TD]
[TD="align: center"]30/09/2018[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]01/01/2018[/TD]
[TD="align: center"]01/07/2018[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]01/01/2018[/TD]
[TD="align: center"]01/07/2018[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]***[/TD]
[TD="align: center"]**********[/TD]
[TD="align: center"]*************[/TD]
[TD="align: center"]*************[/TD]
[TD="align: center"]****[/TD]
[TD="align: center"]********[/TD]
[TD="align: center"]**[/TD]
[TD="align: center"]***[/TD]
[TD="align: center"]**********[/TD]
[TD="align: center"]*************[/TD]
[TD="align: center"]*************[/TD]
[TD="align: center"]****[/TD]
[TD="align: center"]********[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz