# Excel formula help - to find last Quarter

#### Mallesh23

##### Well-known Member
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

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

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### C Moore

##### Well-known Member
Leave that table in your workbook and do index-match

##### Well-known Member
Hi Malesh23,

I am unclear as to which year you use for the calculation:
1. If all quarters must be for the same financial year and you use the Current Year then wouldn't the last entry for Q4 be 31/03/2022?
2. If all quarters must be earlier than the current quarter then wouldn't Q3 be 31/12/2020?

#### Mallesh23

System date.

Thanks
mg

##### Well-known Member

System date.
But if it's the system date then Q4 should be 31/03/2022, shouldn't it?

#### Mallesh23

##### Well-known Member

Ya you are right,

Fiscal Year start from ----April

Thanks
mg

##### Well-known Member
But if they can only select from a previous quarter, not even the current quarter to date, then the calculation should return these dates, shouldn't it?

#### Mallesh23

##### Well-known Member

Agree with you. above dates are correct, future quarters needs to ignore, Only previous quarters to consider. Thanks.

Thanks
mg

##### Well-known Member
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
EFGHI
1Today
215-Jul-21
3Using E3Using TODAY()
4Quarter SelectionEnd of Quarter DateEnd of Quarter Date
5Q331-Dec-2031-Dec-20
Sheet2
Cell Formulas
RangeFormula
E2E2=TODAY()
G5G5=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)
I5I5=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
CellAllowCriteria
E5ListQ1,Q2,Q3,Q4

#### Mallesh23

##### Well-known Member

Thanks for your help, it worked ?

Thanks
mg

Replies
2
Views
178
Replies
7
Views
114
Replies
5
Views
345
Replies
4
Views
2K
Replies
0
Views
69

1,186,112
Messages
5,955,913
Members
438,225
Latest member
rsur

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back