Date Range Period over Period over years

[flashgordie]

Hello, I am hoping this is example is enough to illustrate my point. I've made many attempts to put this into words and this is about as good as I have been able to get. Apologies. Any better description would be welcome as well.

I have a table that contains many transaction dates spanning years. Instead of comparing the transactions by years, quarters or months, I’m looking to establish a range (a starting month and day, to an ending month and day) and use this range to see what year each of the transactions would fall and return that appropriate year. Would prefer formula as opposed to vba.


Example.

Date A= Sept 1-2022
Date B =April 30-2023

Transaction date and what I hope to the formula would return...
Sept 10-2020 returns 2021
Jan 20-2021 returns 2021
Aug 30-2021 returns not in range
Sept 10-2021 returns 2022
Jan 20-2022 returns 2022
Aug 30-2022 returns not in range
Sept 10-2022 returns 2023
Jan 20-2023 returns 2023
Aug 30-2023 returns not in range
Sept 10-2023 returns 2024
Jan 20-2024 returns 2024
Aug 30-2023 returns not in range


Any and all help would be appreciated. Thank you!!!

 

[BigBeachBananas]

Sept 10-2020 is not in range, why does it return to 2021?

 

[bebo021999]

Why:
Sept 10-2020 returns 2021
Jan 20-2021 returns 2021
Sept 10-2022 returns 2023
???

 

[Ezguy4u]

There are some questions that need to be answered.

Book1
	B	C	D
5	Thursday, September 1, 2022		Sept 10-2020 returns 2021
6	October-22		Jan 20-2021 returns 2021
7	November-22		Aug 30-2021 returns not in range
8	December-22		Sept 10-2021 returns 2022
9	January-23		Jan 20-2022 returns 2022
10	February-23		Aug 30-2022 returns not in range
11	March-23		Sept 10-2022 returns 2023
12	Sunday, April 30, 2023		Jan 20-2023 returns 2023
13			Aug 30-2023 returns not in range
14			Sept 10-2023 returns 2024
15			Jan 20-2024 returns 2024
16			Aug 30-2023 returns not in range
Sheet1

 

[flashgordie]

Thank you all for the interest and responses. Here is my pathetic attempt at explaining further. Because of the way the spreadsheet is set up, I have a full date (MM/DD/YY) for Date A and Date B. However, I’m believe the more important thing that needs to be considered it the Month and the Day for Date A; and then to know that B will always fall after Date A and within 1 year. In the previous example where Date A= Sept 1-2022 and Date B =April 30-2023 there are 241 days between these two dates. I have thousands of transactions dates for which I would like the formula to …

determine if the month and day of a transaction date falls withing those 241 days ignoring any particular year of influence. ie

• May 3rd, June 19th, July 12th, Aug 10th – all false
• Sept 3rd, Jan 3rd, Oct 4th, Nov 2nd, Dec 23rd, Jan 12th, Mar 25th , April 1st– all True

For anything false, we can indicate “not in range”

Now the complicated part to explain, and I have revised my thinking from my initial forum post…..For anything true, we would like to return a value that would group the range related transactions together.

• 9/3/2017 would return 2017
• 1/22/2018 would return 2017
• 10/4/2018 would return 2018
• 1/21/2019 would return 2018
• 11/2/2019 would return 2019
• 3/1/2020 would return 2019
• 12/23/2020 would return 2020
• 1/12/2021 would return 2020
• 9/2/2021 would return 2021
• 3/25/2022 would return 2021
• 10/1/2022 would return 2022
• 4/1/2023 would return 2022
• 9/25/2023 would return 2023
• 4/22/2024 would return 2023

For the second line 1/22/2018, it falls withing the period, but I need it to be “grouped” with all the other transactions that also fall withing that date range and those 241 days. Both the 9/3/2017, and the 1/22/2018 could return “Group xyz” but I’m thinking a formula would be better suited to extract the year somehow and assign the year of the earliest date.

Beyond that, I think it would be important to consider that the range could be altered to something else ie Jan 15th to March 3rd – 48 days, by which in this case both Date A and Date B would have the same year. I believe the year in Date A and Date B are of no matter as I could use Sept 1-1900 and April 30-1901, or Jan 15-1900 and March 3-1900. To further illustrate, if it was the Jan 15 to Mar 3 range then the following would be the result…

• 9/3/2017 would return 2017
• 1/3/2018 would return 2017
• 9/3/2017 not in Range
• 1/22/2018 would return 2018
• 10/4/2018 not in Range
• 1/21/2019 would return 2019
• 11/2/2019 not in Range
• 3/1/2020 would return 2020
• 12/23/2020 not in Range
• 1/12/2021 not in Range
• 9/2/2021 not in Range
• 3/25/2022 not in Range
• 10/1/2022 not in Range
• 4/1/2023 not in Range
• 9/25/2023 not in Range
• 4/22/2024 not in Range

 

[Tetra201]

... Date B will always fall after Date A and within 1 year...

Assuming you have Date A in cell F2 and Date B in cell G2, see if the following formula works for you:

=LET(n,12*(YEAR(A2)-IF(OR(YEAR($F$2)=YEAR($G$2),MONTH(A2)<=MONTH($G$2)),YEAR($G$2),YEAR($F$2))),d,EDATE(A2,-n),IF(d=MEDIAN(d,$F$2,$G$2),YEAR(MIN(A2,EDATE(+$F$2:$G$2,n))),"not in range"))

 

[flashgordie]

Assuming you have Date A in cell F2 and Date B in cell G2, see if the following formula works for you:

=LET(n,12*(YEAR(A2)-IF(OR(YEAR($F$2)=YEAR($G$2),MONTH(A2)<=MONTH($G$2)),YEAR($G$2),YEAR($F$2))),d,EDATE(A2,-n),IF(d=MEDIAN(d,$F$2,$G$2),YEAR(MIN(A2,EDATE(+$F$2:$G$2,n))),"not in range"))

This looks really promising. Thank you so much for the help. I know the explanation I provided was probably confusing but I believe this should help. Cheers!

 

[flashgordie]

Hello Again, Hopefully this would be my last question. Thanks again for all the assistance. The formula provided works well when "manually" placed however I have a vba in which I am trying to have the code insert the formula into the cell.

Unfortunately I keep getting a error: Run-time error '1004':Application-defined or object-defined error

I believe it has something to do with the double quotation marks the surround the ""not in range"" aspect of the formula.

newColumn.DataBodyRange.Cells(1).Resize(1, 1).FormulaR1C1 = "=LET(n,12*(YEAR(D12)-IF(OR(YEAR($G$4)=YEAR($G$5),MONTH(D12)<=MONTH($G$5)),YEAR($G$5),YEAR($G$4))),d,EDATE(D12,-n),IF(d=MEDIAN(d,$G$4,$G$5),YEAR(MIN(D12,EDATE(+$G$4:$G$5,n))),""not in range""))"

Any help would be greatly appreciated.

Cheers