Excel formula help - to find last Quarter

[Mallesh23]

Hi Team,

I have created one userform if user select Q1 from ComboBox

I should get first Quarter end Date of Current year. 30/06/2021
if user select second Q2 I should get 2nd Quarter end Date 30/09/2021


my quarters start from Apr-Jun, Onward. User download Previous quarters Result.

Below is table. Expected output is in Column B.

Quarter	Last_Quarter_Date	Quarter Begins
Q1	30/06/2021	Apr-Jun
Q2	30/09/2021	July-Sep
Q3	31/12/2021	Oct-Dec
Q4	31/03/2020	Jan-Mar

Thanks
mg



Thanks
mg

 

[Toadstool]

Hi Toadstool,
Thanks for your help, it worked ?
Thanks
mg

You're welcome!
Please also mark post #9 as a solution. Thanks!

 

[James Clear]

Hi Mallesh23,

So it's a little complex because it has to use the current quarter to make sure the year is correct for the selected quarter, an earlier quarter.

Cell E5 contains the Quarter selection but I've given you E2 and G5 to test. So set any test date in E2 to check that G5 calculates the correct end date for the selected quarter. If you're OK with the calculation then I5 uses the system date so you can paste that into your worksheet.

Mallesh23.xlsx
	E	F	G	H	I
1	Today
2	15-Jul-21
3			Using E3		Using TODAY()
4	Quarter Selection		End of Quarter Date		End of Quarter Date
5	Q3		31-Dec-20		31-Dec-20
Sheet2

Cell Formulas
Range	Formula
E2	E2	=TODAY()
G5	G5	=EOMONTH(DATE(YEAR($E$2)-(INDEX({6,9,12,3},RIGHT(E5,1))>=MONTH($E$2)),INDEX({6,9,12,3},RIGHT(E5,1)),1),0)
I5	I5	=EOMONTH(DATE(YEAR(TODAY())-(INDEX({6,9,12,3},RIGHT(E5,1))>=MONTH(TODAY())),INDEX({6,9,12,3},RIGHT(E5,1)),1),0)

Cells with Data Validation
Cell	Allow	Criteria
E5	List	Q1,Q2,Q3,Q4

Hi Toadstool,

There is a lot to learn from you ….could you please explain this formula to me G5 & I5

 

[Toadstool]

Hi Toadstool,

There is a lot to learn from you ….could you please explain this formula to me G5 & I5

The G5 formula was just so the OP could test with different TODAY() dates by entering them in E2. The proper formula using TODAY() as the base is in I5.

Let's start with INDEX({6,9,12,3},RIGHT(E5,1) which appears twice in the formulae.
The Quarter years run from April 1st so a quarter end is always June, September, December or March. The RIGHT(E5,1) gets the quarter number, so for W3 it retrieves 3.
Normally for INDEX you're retrieving from a range of cells but you can embed that range as an array constant in the formula by surrounding in curly brackets, so INDEX({6,9,12,3},3 will return the third entry which is a 12 for December quarter end.

The tricky part here is that a date is specified with a year but the quarter year may be an earlier year (e.g. specify Q2 with a date of 2 Feb 2021 but that would give quarter end of 31 March 2021 but 2 Feb 2021 is still in quarter year 2020 so we want to subtract a year). That's where -(INDEX({6,9,12,3},RIGHT(E5,1))>=MONTH(TODAY()) is used.
If today is 11 July 2021 and we're specifying Q3 then it checks if 12 is greater than 7 which returns a TRUE (logical 1) so it subtracts 1 from the current year because that must be Q3 of the previous calendar year.

The EOMONTH then returns the last day of the month for the specified year and month, which is the quarter end.

 

[James Clear]

Superb will check in my laptop tomorrow and study this if any clarification required will bother you … thanks a lot for your assistance with this matter.???

One more question what if I have to get Q4 as a next year 2022
Coz financial year starts from Apr’21 to June’21 as a Q1
Jul21 to Sep 21 Q2 Oct 21 to Dec 21 as Q3 n then Jan 22 to Mar 22 Q4

How can I derive that? Pls help

 

[Toadstool]

The OP was looking at historical data. If you may be looking at historical or future quarters then the FY would be needed to clarify, e.g. FY22Q3 and not just Q3.

 

[James Clear]

Y

The OP was looking at historical data. If you may be looking at historical or future quarters then the FY would be needed to clarify, e.g. FY22Q3 and not just Q3.

Yes, I meant to say FY22Q3