Formula determine if a date falls between a specific M/D range for multiple years for YTD comparison.

[flashgordie]

Hello, I have a report that consists of transactions over a number of years. In E12, I have added a column and used =YEAR(D12)+(MONTH(D12)>=12) to calculate which fiscal year a transaction fall into as out year runs Dec 1 to Nov 30.

• I would like to add a second column (F) and I am hoping someone could suggest another formula that could be added starting at F12 to calculate if the transaction date (Month and Day) falls within a range, if so, return the fiscal year. It if does not, it would be considered “out of range”.
  
  
• The range would be specified by the user by entering a starting point (F4, month and day) and an end point (F5, month and day). Although maybe I’m over thinking this, but I wonder if this will create its own challenge as dates in excel consist of all 3 elements – MM/DD/YY, but I am hoping we could find a solution so that I would just use month and day.

Any help would be appreciated.

Thank you!

Book11
	A	B	C	D	E	F	G
1
2
3
4						1-Dec	Start Date
5						24-May	End date
6
7
8
9
10					Dec 1 to Nov 30
11				Trans Date	Fiscal Period	YTD Range
12				11/30/2015	2015	"not in range"
13				12/1/2015	2016	2016
14				1/12/2016	2016	2016
15				5/6/2016	2016	2016
16				7/6/2016	2016	2016
17				11/20/2016	2016	2016
18				1/25/2017	2017	2017
19				7/1/2017	2017	"not in range"
20				12/21/2017	2018	2018
21				5/2/2018	2018	2018
22				6/2/2018	2018	"not in range"
23				11/16/2018	2018	"not in range"
24				1/21/2019	2019	2019
25				8/4/2019	2019	"not in range"
26				10/12/2019	2019	"not in range"
27				11/30/2019	2019	"not in range"
28				12/1/2019	2020	2020
29				2/21/2020	2020	2020
30				6/6/2020	2020	"not in range"
31				11/11/2020	2020	"not in range"
32				1/16/2021	2021	2021
33				3/23/2021	2021	2021
34				12/12/2021	2022	2022
35				2/16/2022	2022	2022
36				11/7/2022	2022	"not in range"
37				1/12/2023	2023	2023
38				5/23/2023	2023	2023
39				5/24/2023	2023	2023
40				5/25/2023	2023	"not in range"
Sheet1

Cell Formulas
Range	Formula
E12:E40	E12	=YEAR(D12)+(MONTH(D12)>=12)

 

[JamesCanale]

MrExcelPlayground17.xlsx
	D	E	F	G
4			1-Dec	Start Date
5			24-May	End date
6
7
8
9
10		Dec 1 to Nov 30
11	Trans Date	Fiscal Period	YTD Range
12	11/30/2015	2015	"not in range"	not in range
13	12/1/2015	2016	2016	2016
14	1/12/2016	2016	2016	2016
15	5/6/2016	2016	2016	2016
16	7/6/2016	2016	2016	not in range
17	11/20/2016	2016	2016	not in range
18	1/25/2017	2017	2017	2017
19	7/1/2017	2017	"not in range"	not in range
20	12/21/2017	2018	2018	2018
21	5/2/2018	2018	2018	2018
22	6/2/2018	2018	"not in range"	not in range
23	11/16/2018	2018	"not in range"	not in range
24	1/21/2019	2019	2019	2019
25	8/4/2019	2019	"not in range"	not in range
26	10/12/2019	2019	"not in range"	not in range
27	11/30/2019	2019	"not in range"	not in range
28	12/1/2019	2020	2020	2020
29	2/21/2020	2020	2020	2020
30	6/6/2020	2020	"not in range"	not in range
31	11/11/2020	2020	"not in range"	not in range
32	1/16/2021	2021	2021	2021
33	3/23/2021	2021	2021	2021
34	12/12/2021	2022	2022	2022
35	2/16/2022	2022	2022	2022
36	11/7/2022	2022	"not in range"	not in range
37	1/12/2023	2023	2023	2023
38	5/23/2023	2023	2023	2023
39	5/24/2023	2023	2023	2023
40	5/25/2023	2023	"not in range"	not in range
Sheet20

Cell Formulas
Range	Formula
E12:E40	E12	=YEAR(D12)+(MONTH(D12)=12)
G12:G40	G12	=LET(m,MONTH(D12),d,DAY(D12),mm,MONTH($F$4),dd,DAY($F$4),mmm,MONTH($F$5),ddd,DAY($F$5),t,DATE(IF(m=12,1999,2000),m,d),tt,DATE(IF(mm=12,1999,2000),mm,dd),ttt,DATE(IF(mmm<12,2000,1999),mmm,ddd),IF(AND(t>=tt,t<=ttt),E12,"not in range"))

 

[flashgordie]

=LET(m,MONTH(D12),d,DAY(D12),mm,MONTH($F$4),dd,DAY($F$4),mmm,MONTH($F$5),ddd,DAY($F$5),t,DATE(IF(m=12,1999,2000),m,d),tt,DATE(IF(mm=12,1999,2000),mm,dd),ttt,DATE(IF(mmm<12,2000,1999),mmm,ddd),IF(AND(t>=tt,t<=ttt),E12,"not in range"))

Hi James, Thank you so much for helping me. This is very close. sooooo close. However, I noticed that when I adjust the start date from Dec 1st to an earlier time , the formula indicates none of the transaction dates are within range. Any ideas?

 

[JamesCanale]

I have it with the fiscal year. So from what I figured, starting before Dec 1 wouldn't make any sense. But I guess it does make sense.

For example, if you wanted to see between Nov 1 and Jan 30, Anything in November would be the prior fiscal year.

 

[flashgordie]

I have it with the fiscal year. So from what I figured, starting before Dec 1 wouldn't make any sense. But I guess it does make sense.

For example, if you wanted to see between Nov 1 and Jan 30, Anything in November would be the prior fiscal year.

Yes, you are correct. For the most part it is likely to start with the fiscal year, but we also deal with seasonality. Which means I might be looking to compare Nov 1 to Mar 1 (winter months) from 1 year to the next. Obviously the date range to be compared YOY could not be any greater than 365 days. Would you have any options for this? Again, Thanks very much for your help!!!

 

[Tetra201]

See if the following formula works for you:

=IF(MOD(TEXT(D12,"mmdd")-TEXT($F$4,"mmdd"),1200)+MOD(TEXT($F$5,"mmdd")-TEXT(D12,"mmdd"),1200)=MOD(TEXT($F$5,"mmdd")-TEXT($F$4,"mmdd"),1200),E12,"Not in range")

 

[flashgordie]

@JamesCanale and @Tetra201 ..... Thank you so much for your help. Both answers work well with each doing something a little different. In @JamesCanale case, it sees the start date of the range being equal to the start of the fiscal year, while in @Tetra201 solution, the start and end of the date range can be altered. I very much appreciate the help!!!