Next Quarter in Excel

[lisa_swinton]

hi All

I'm trying to find a formula that can give the next quarter based on the current quarter

This is my formula to generate the current quarter but not finding a simple way
="Q"&INT((MONTH(U2)+2)/3)&"-"&YEAR(U2)

Suggestions welcome I suspect this is a very simple answer I'm just not finding it.

 

[ouvay]

Hi, is this what you're looking for?
It should always show you the following quarter and its based on today()

="Q"&INT((MONTH(DATE(YEAR(TODAY()), ((INT((MONTH(TODAY())-1)/3)+1)*3)+1, 1))+2)/3)&"-"&YEAR(DATE(YEAR(TODAY()), ((INT((MONTH(TODAY())-1)/3)+1)*3)+1, 1))

Alternatively, you could keep your date in U2 and use this

="Q"&INT((MONTH(DATE(YEAR(U2), ((INT((MONTH(U2)-1)/3)+1)*3)+1, 1))+2)/3)&"-"&YEAR(DATE(YEAR(U2), ((INT((MONTH(U2)-1)/3)+1)*3)+1, 1))

 

[billszysz]

=LET(q, XLOOKUP(LEFT(U2,2),{"Q1","Q2","Q3","Q4"},{"Q2","Q3","Q4","Q1"}),
q & "-" & RIGHT(U2,4)+(q="Q1"))

 

[lisa_swinton]

Hi, is this what you're looking for?
It should always show you the following quarter and its based on today()

="Q"&INT((MONTH(DATE(YEAR(TODAY()), ((INT((MONTH(TODAY())-1)/3)+1)*3)+1, 1))+2)/3)&"-"&YEAR(DATE(YEAR(TODAY()), ((INT((MONTH(TODAY())-1)/3)+1)*3)+1, 1))

Alternatively, you could keep your date in U2 and use this

="Q"&INT((MONTH(DATE(YEAR(U2), ((INT((MONTH(U2)-1)/3)+1)*3)+1, 1))+2)/3)&"-"&YEAR(DATE(YEAR(U2), ((INT((MONTH(U2)-1)/3)+1)*3)+1, 1))

Hi I think the second one is closer but then can't I use the same to then add +2 quarters, +3 etc I have a table that has space for 14 quarters of data

CSA Date:	30-Sep-22
Ref PMR:
	Q3-2022	Q4-2022	Q1-2023	Q2-2023	Q3-2023	Q4-2023	Q1-2024

 

[FormR]

Hi, here's another option you can try:

=LET(d,EDATE(U2,0*3),"Q"&ROUNDUP(MONTH(d)/3,0)&"-"&YEAR(d))

Adjust the bold number for the number of quarters you want to advance the date by i.e. leave as 0 for the current quarter, change to 1 for the next etc.

 

[lisa_swinton]

Using that one is returning a #NAME? Error and I checked that U2 value is set as date format

Hi, here's another option you can try:

=LET(d,EDATE(U2,0*3),"Q"&ROUNDUP(MONTH(d)/3,0)&"-"&YEAR(d))

Adjust the bold number for the number of quarters you want to advance the date by i.e. leave as 0 for the current quarter, change to 1 for the next etc.

 

[FormR]

Using that one is returning a #NAME? Error

That would suggest that you don't have an up to date 365 version of Excel and are likely missing the Let() function. Without Let() you could try:

="Q"&ROUNDUP(MONTH(EDATE(U2,0*3))/3,0)&"-"&YEAR(EDATE(U2,0*3))

Where you would need to adjust the increment in two places.

 

[lisa_swinton]

That would suggest that you don't have an up to date 365 version of Excel and are likely missing the Let() function. Without Let() you could try:

="Q"&ROUNDUP(MONTH(EDATE(U2,0*3))/3,0)&"-"&YEAR(EDATE(U2,0*3))

Where you would need to adjust the increment in two places.

That did it, thanks for the help