Excel formula help - to find last Quarter

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
957
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
579
Office Version
  1. 365
Platform
  1. Windows
Leave that table in your workbook and do index-match
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,913
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
957
Office Version
  1. 2010
Platform
  1. Windows
Hi Toadstool,

System date.


Thanks
mg
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,913
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi Toadstool,

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

Mallesh23

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

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,913
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
957
Office Version
  1. 2010
Platform
  1. Windows
Hi ToadStool,

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


Thanks
mg
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,913
Office Version
  1. 2016
Platform
  1. Windows
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
 
Solution

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
957
Office Version
  1. 2010
Platform
  1. Windows
Hi Toadstool,

Thanks for your help, it worked (y) 🕺


Thanks
mg
 

Forum statistics

Threads
1,141,061
Messages
5,704,043
Members
421,324
Latest member
Devo182

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
Top