need to arrive the end of the Quarter end date

[ahmed18]

Hi Everyone,

My Financial year Starts from November to October and I need to arrive at the end of the quarter's-end date. Please help with excel formulas or functions at the earliest.

Nov to Jan - Q1
Feb to Apr -Q2
May to July - Q3
Aug to Oct - Q4
Example -
12-Dec-2020 My QEC end date will be 31st Jan 2021
11-Aug-2021 My QEC end date will be 31st Oct 2021

Thanks in advance please help.

 

[Dave Patton]

The 3rd alternative names the lookup array. Copy the array from B3 and name the array with Name Manage Value ={44136,44227;44228,44316;44317,44408;44409,44500}
Try the alternative that you prefer. The table is not required for the last two alternatives.

T202203a.xlsm
	A	B	C	D	E
1
2	12-Dec-20	31-Jan-21		1-Nov-20	31-Jan-21
3		31-Jan-21		1-Feb-21	30-Apr-21
4		31-Jan-21		1-May-21	31-Jul-21
5				1-Aug-21	31-Oct-21
6
2a

Cell Formulas
Range	Formula
E2:E5	E2	=EOMONTH(D2,2)
D3:D5	D3	=E2+1
B2	B2	=LOOKUP(A2,D2:E5)
B3	B3	=LOOKUP(A2,{44136,44227;44228,44316;44317,44408;44409,44500})
B4	B4	=LOOKUP(A2,EndQ)

 

[jtakw]

Hi,

See if this does what you need:

Book3.xlsx
	A	B
1	Date	Qtr End Date
2	12/12/2020	1/31/2021
3	8/11/2021	10/31/2021
4	11/20/2021	1/31/2022
Sheet1041

Cell Formulas
Range	Formula
B2:B4	B2	=EOMONTH(DATE(YEAR(A2),CEILING(MONTH(A2),3),1),1)

 

[Dave Patton]

A more generic version.

T202203a.xlsm
	A	B	C	D
7	5-Aug-21		31-Oct-21	31-Oct-21
8	5-Sep-21		31-Oct-21	31-Oct-21
9	5-Oct-21		31-Oct-21	31-Oct-21
10	15-Jul-21		31-Jul-21	31-Jul-21
11
2a

Cell Formulas
Range	Formula
C7	C7	=EOMONTH(A7,2)
D7	D7	=EOMONTH(A7,LOOKUP(MONTH(A7),{11,2;12,1;1,0;2,2;3,1;4,0;5,2;6,1;7,0;8,2;9,1;10,0}))
C8	C8	=EOMONTH(A8,1)
D8:D10	D8	=EOMONTH(A8,LOOKUP(MONTH(A8),End_Q))
C9:C10	C9	=EOMONTH(A9,0)

 

[Dave Patton]

Ignore post #4.

T202203a.xlsm
	A	B	C	D
1	Mon 10-Jan-22	31-Jan-22	31-Jan-22	31-Jan-22
2	Thu 10-Feb-22	30-Apr-22	30-Apr-22	30-Apr-22
3	Thu 10-Mar-22	30-Apr-22	30-Apr-22	30-Apr-22
4	Sun 10-Apr-22	30-Apr-22	30-Apr-22	30-Apr-22
5	Tue 10-May-22	31-Jul-22	31-Jul-22	31-Jul-22
6	Fri 10-Jun-22	31-Jul-22	31-Jul-22	31-Jul-22
7	Sun 10-Jul-22	31-Jul-22	31-Jul-22	31-Jul-22
8	Wed 10-Aug-22	31-Oct-22	31-Oct-22	31-Oct-22
9	Sat 10-Sep-22	31-Oct-22	31-Oct-22	31-Oct-22
10	Mon 10-Oct-22	31-Oct-22	31-Oct-22	31-Oct-22
11	Thu 10-Nov-22	31-Jan-23	31-Jan-23	31-Jan-23
12	Sat 10-Dec-22	31-Jan-23	31-Jan-23	31-Jan-23
13	Tue 10-Jan-23	31-Jan-23	31-Jan-23	31-Jan-23
14	Fri 10-Feb-23	30-Apr-23	30-Apr-23	30-Apr-23
15	Fri 10-Mar-23	30-Apr-23	30-Apr-23	30-Apr-23
16	Sat 12-Dec-20	31-Jan-21	31-Jan-21	31-Jan-21
17	Wed 11-Aug-21	31-Oct-21	31-Oct-21	31-Oct-21
18	Wed 15-Jul-20	31-Jul-20	31-Jul-20	31-Jul-20
19
2a

Cell Formulas
Range	Formula
B1:B18	B1	=EOMONTH(A1,OR(MONTH(A1)={3,6,9,12})*1+OR(MONTH(A1)={2,5,8,11})*2+OR(MONTH(A1)={1,4,7,10})*0)
C1:C18	C1	=EOMONTH(A1,LOOKUP(MONTH(A1),{1,0;2,2;3,1;4,0;5,2;6,1;7,0;8,2;9,1;10,0;11,2;12,1}))
D1:D18	D1	=EOMONTH(A1,LOOKUP(MONTH(A1),End_Q))