Excel formula help - to find last Quarter

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
976
Office Version
  1. 2010
Platform
  1. Windows
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.

QuarterLast_Quarter_DateQuarter Begins
Q130/06/2021Apr-Jun
Q230/09/2021July-Sep
Q331/12/2021Oct-Dec
Q431/03/2020Jan-Mar



Thanks
mg



Thanks
mg
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Leave that table in your workbook and do index-match
 
Upvote 0
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?

Please advise what decides the year for a selected quarter.
 
Upvote 0
Hi Toadstool,

Ya you are right,
User looks /downloads Previous Quarters reports, Quarter 4 should be 31/03/2020.

Fiscal Year start from ----April

Thanks
mg
 
Upvote 0
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?

1626296462009.png
 
Upvote 0
Hi ToadStool,

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


Thanks
mg
 
Upvote 0
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
 
Upvote 0
Solution

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top