Excel formula to read prior quarter as "ABC Corp Q (#) yyyy taxpayment"?

[streets]

I desire an automatically updating excel formula, or formulas, that wiIl read the prior quarter as like "Q2 2024" and "2nd quarter 2024", with using the CONCATENATE formula, or similar functioning formula excel feature.

In order for excel to read the latest calendar year quarter, displaying digits for the date in the form of mm/dd/yyyy , there is this formula I use :

=TEXT(EOMONTH(TODAY(),-4)+1,"mm/dd/yyyy")&" - "&TEXT(EOMONTH(TODAY(),-1),"mm/dd/yyyy")

Presently, as of today's July 2024 date, that will show-up in excel as "04/01/2024 - 06/30/2024". Good. I desire to also use the formula, or similar type of formula, to read as, (1) "ABC Corp Q2 2024 tax payment to IRS" and (2) "ABC Corp 2nd quarter 2024 tax payment to IRS". The "ABC Corp" and the "tax payment to IRS" parts of it never change. The quarter of the year part, however, is always the latest quarter of year, which one that happens to be, as of today's date. I'd like to have excel formula for it to auto-update, to prevent human typing error. How to make it?

 

[hagia_sofia]

Something like this (though I am not sure how quarterly period is defined in this case)?

="ABC Corp "&LET(
t,TODAY(),
IFS(AND(t>=DATE(YEAR(t),1,1),t<=DATE(YEAR(t),3,31)),"Q1 "&YEAR(t),AND(t>=DATE(YEAR(t),4,1),t<=DATE(YEAR(t),6,30)),"Q2 "&YEAR(t),AND(t>=DATE(YEAR(t),7,1),t<=DATE(YEAR(t),9,30)),"Q3 "&YEAR(t),AND(t>=DATE(YEAR(t),10,1),t<=DATE(YEAR(t),12,31)),"Q4 "&YEAR(t)))& " tax payment to IRS"

="ABC Corp "&LET(
t,TODAY(),
IFS(AND(t>=DATE(YEAR(t),1,1),t<=DATE(YEAR(t),3,31)),"1st quarter "&" "&YEAR(t),AND(t>=DATE(YEAR(t),4,1),t<=DATE(YEAR(t),6,30)),"2nd quarter "&" "&YEAR(t),AND(t>=DATE(YEAR(t),7,1),t<=DATE(YEAR(t),9,30)),"3rd quarter"&" "&YEAR(t),AND(t>=DATE(YEAR(t),10,1),t<=DATE(YEAR(t),12,31)),"4th quarter "&" "&YEAR(t)))& " tax payment to IRS"

 

[streets]

Unfortunately it doesn't hit the spot, but I thank you for the help so far, in trying to help me figure this out. I think what you have there for me is mostly right, just a little off. It needs to be tweaked. Could you please tweak it?

The calendar year quarter for the formula to output is the latest completed prior quarter, and not the quarter of the year we are presently in, date-wise. Today's date is in July 2024, which is in 3rd quarter 2024, or 3Q 2024. The tax payments to IRS to which I refer are made in reference to the most recent quarter that has just ended, not long ago. For this formula, I want it to auto update to the latest prior quarter, which for today's date in July 2024 means it would auto-update for the 2nd quarter 2024, covering calendar date range 04/01/2024 - 06/30/2024.

 

[awoohaw]

How about:

Book1
	A	B	C
1
2	2024-01-20	4	ABC Corp Q4-2023
3	2024-02-20	4	ABC Corp Q4-2023
4	2024-03-20	4	ABC Corp Q4-2023
5	2024-04-20	1	ABC Corp Q1-2024
6	2024-05-20	1	ABC Corp Q1-2024
7	2024-06-20	1	ABC Corp Q1-2024
8	2024-07-20	2	ABC Corp Q2-2024
9	2024-08-20	2	ABC Corp Q2-2024
10	2024-09-20	2	ABC Corp Q2-2024
11	2024-10-20	3	ABC Corp Q3-2024
12	2024-11-20	3	ABC Corp Q3-2024
13	2024-12-20	3	ABC Corp Q3-2024
Sheet1

Cell Formulas
Range	Formula
B2:B13	B2	=IF(INT((MONTH(A2)-1)/3)=0,4,INT((MONTH(A2)-1)/3))
C2:C13	C2	= "ABC Corp Q" & IF(INT((MONTH(A2)-1)/3)=0,4,INT((MONTH(A2)-1)/3)) &"-"& YEAR(A2)-IF(INT((MONTH(A2)-1)/3)=0,1,0)

 

[streets]

awoohaw, awesome. I appreciate you digging into this for me.

 

[awoohaw]

awoohaw, awesome. I appreciate you digging into this for me.

Did this work as you wanted?

Best wishes!

 

[streets]

It does one of the two exactly like I want. The solution you provided so far takes care of the # 1, which is automating the "ABC Corp Q2 2024 tax payment to IRS" line.

The other line I desire automation for is # 2, for the line "ABC Corp 2nd quarter 2024 tax payment to IRS". In this # 2, the number of the quarter is displayed first and using the ordinal numbering system, of "1st", "2nd", "3rd", "4th", followed by the word "quarter", then the year (which "2024" is the most recent prior quarter's end). Is it possible to get a formula set for # 2?

 

[awoohaw]

how about:

Book1
	A	B	C	D	E
1
2	2024-01-20	4	ABC Corp Q4-2023	4th	ABC Corp 4th quarter, 2023 tax payment to IRS
3	2024-02-20	4	ABC Corp Q4-2023	4th	ABC Corp 4th quarter, 2023 tax payment to IRS
4	2024-03-20	4	ABC Corp Q4-2023	4th	ABC Corp 4th quarter, 2023 tax payment to IRS
5	2024-04-20	1	ABC Corp Q1-2024	1st	ABC Corp 1st quarter, 2024 tax payment to IRS
6	2024-05-20	1	ABC Corp Q1-2024	1st	ABC Corp 1st quarter, 2024 tax payment to IRS
7	2024-06-20	1	ABC Corp Q1-2024	1st	ABC Corp 1st quarter, 2024 tax payment to IRS
8	2024-07-20	2	ABC Corp Q2-2024	2nd	ABC Corp 2nd quarter, 2024 tax payment to IRS
9	2024-08-20	2	ABC Corp Q2-2024	2nd	ABC Corp 2nd quarter, 2024 tax payment to IRS
10	2024-09-20	2	ABC Corp Q2-2024	2nd	ABC Corp 2nd quarter, 2024 tax payment to IRS
11	2024-10-20	3	ABC Corp Q3-2024	3rd	ABC Corp 3rd quarter, 2024 tax payment to IRS
12	2024-11-20	3	ABC Corp Q3-2024	3rd	ABC Corp 3rd quarter, 2024 tax payment to IRS
13	2024-12-20	3	ABC Corp Q3-2024	3rd	ABC Corp 3rd quarter, 2024 tax payment to IRS
Sheet1

Cell Formulas
Range	Formula
B2:B13	B2	=IF(INT((MONTH(A2)-1)/3)=0,4,INT((MONTH(A2)-1)/3))
C2:C13	C2	= "ABC Corp Q" & IF(INT((MONTH(A2)-1)/3)=0,4,INT((MONTH(A2)-1)/3)) &"-"& YEAR(A2)-IF(INT((MONTH(A2)-1)/3)=0,1,0)
D2:D13	D2	= CHOOSE(INT((MONTH(A2)-1)/3)+1,"4th","1st","2nd","3rd")
E2:E13	E2	="ABC Corp " & CHOOSE(INT((MONTH(A2)-1)/3)+1,"4th","1st","2nd","3rd") & " quarter, " & YEAR(A2) - IF(INT((MONTH(A2)-1)/3)=0,1,0) & " tax payment to IRS"

 

[streets]

awoohaw, thanks again. With these two automated excel formulas in-place, it is decreased, by quite a bit, the chance of human typing error of the quarter number and year, and/or not updating quarter number and year, for each quarter when taxpayment calculations are prepared.

 

[awoohaw]

You're welcome. Thanks for the feedback.

Best Wishes!