Adding only academic months to a date

[viktoriacarr]

I am brand new to this thread and am not the most experienced Excel user so maybe I am missing something simple and I would so appreciate anyone's help

I work in the school system with multilanguage learners and when these students come to the USA from another county, we receive federal funding to support them for 33 academic months. I know how to add months to a date so that's no problem but I was wondering if there was any way to add only academic months to a date.

For example, Student A's first day in a US school is 1/4/23 and we are able to claim them for 33 academic months. Is there is a way to add months to a date so I can track when their funding will end while NOT counting the months of June, August, and July to the total number of months?

Thank you in advance for any advice you can give me!! If you need any additional details please let me know

 

[viktoriacarr]

How about

Fluff.xlsm
	A	B
1
2	01/04/2023	01/01/2027
3	11/12/2023	11/09/2027
4	31/07/2023	28/02/2027
Sheet6

Cell Formulas
Range	Formula
B2:B4	B2	=LET(d,EDATE(A2,SEQUENCE(50)),CHOOSEROWS(FILTER(d,(MONTH(d)<6)+(MONTH(d)>8)),33))

When I try that formula I get an error that the function isn't valid

 

[Fluff]

Are you using 365 as per your profile?

 

[Dave Patton]

My quick thought was the formula in D2
I included Fluff's suggestion.
Please test with your dates.

Dates Time.xlsm
	A	B	C	D	E
1
2	01-Jul-23	FALSE	0	01-Feb-27	01-Feb-27
3	01-Aug-23	FALSE	0
4	01-Sep-23	TRUE	1
4n

Cell Formulas
Range	Formula
B2:B4	B2	=OR(MONTH(A2)={1,2,3,4,5,9,10,11,12})
C2:C4	C2	=COUNTIF($B$2:B2,TRUE)
D2	D2	=EDATE(A2,33+10-(OR(MONTH(A2)={1,2,3,4,5,9,10,11,12})*2))
E2	E2	=LET(d,EDATE(A2,SEQUENCE(50)),CHOOSEROWS(FILTER(d,(MONTH(d)<6)+(MONTH(d)>8)),33))
A3:A4	A3	=EDATE(A2,1)

 

[viktoriacarr]

Are you using 365 as per your profile?

Oops my apologizes I was using my desktop version which I forgot to list but when trying it in 365 it works great!! Would there happen to be another version of that formula that would work on the desktop version of Excel as well?

 

[Fluff]

What version are you running on the desktop?

 

[viktoriacarr]

What version are you running on the desktop?

Excel 2019

 

[Fluff]

Ok, how about

=AGGREGATE(15,6,EDATE(A2,(ROW($A$1:$A$50)-ROW($A$1)+1))/((MONTH(EDATE(A2,ROW($A$1:$A$50)-ROW($A$1)+1))<6)+(MONTH(EDATE(A2,ROW($A$1:$A$50)-ROW($A$1)+1))>8)),33)

But if you normally need to use 2019, please update your profile.

 

[Dave Patton]

It is possible that the sequence of dates should start with the date in A2,
the formula would then be as follows. Please test with your data.

Dates Time.xlsm
	A	B	C	D	E
2	01-Jan-23	TRUE	1		01-Sep-26
4n

Cell Formulas
Range	Formula
B2	B2	=OR(MONTH(A2)={1,2,3,4,5,9,10,11,12})
C2	C2	=COUNTIF($B$2:B2,TRUE)
E2	E2	=LET(d,EDATE(A2,SEQUENCE(50,,0)),CHOOSEROWS(FILTER(d,(MONTH(d)<6)+(MONTH(d)>8)),33))

 

[viktoriacarr]

It is possible that the sequence of dates should start with the date in A2,
the formula would then be as follows. Please test with your data.

Dates Time.xlsm
	A	B	C	D	E
2	01-Jan-23	TRUE	1		01-Sep-26
4n

Cell Formulas
Range	Formula
B2	B2	=OR(MONTH(A2)={1,2,3,4,5,9,10,11,12})
C2	C2	=COUNTIF($B$2:B2,TRUE)
E2	E2	=LET(d,EDATE(A2,SEQUENCE(50,,0)),CHOOSEROWS(FILTER(d,(MONTH(d)<6)+(MONTH(d)>8)),33))

this is perfect!! thank you so much!

 

[Fluff]

I thought you said that the formula I posted in post#10 worked, but that you needed a formula that works in 2019?