Formula for calculating between 2 dates

[aystrawduh]

Good morning ! I am in need of assistance with coming up with a formula that’ll provide me with an 2 month and 5 month evaluation dates for work without counting a summer period. (05/31/24-08/11/24)

So let’s say a Hire Date is 01/01/24
2 month would be 03/01/24
5 month would be 05/01/24

But if a hire date is 04/10/24 the 2 month and 5 month would need to be adjusted to remove the summer period.

I hope this makes sense and I appreciate the help in advance!!

 

[awoohaw]

I'm not sure how you want to count the endpoint days. But, this may be something you can start with:
(in your example you only added 4 to the date to get 5/1/2024, if you add 5, it goes into June).

Book1
	A	B	C
1	Holiday Start	Holiday End
2	2024-05-31	2024-08-11
3
4		Month out	Eval Date
5	2024-01-01	2	2024-03-01
6	2024-01-01	5	2024-08-12
7	2024-04-10	2	2024-08-21
8	2024-04-10	5	2024-11-21
Sheet1

Cell Formulas
Range	Formula
C5:C8	C5	=IF(EDATE($A5,$B5)<$A$2,EDATE($A5,$B5),EDATE($A5,$B5)-$A$2+$B$2)

 

[Scott R]

2 months out: =LET( dateList, EDATE(A5, SEQUENCE(12)), INDEX( FILTER( dateList, ( dateList <= DATE( 2024, 5, 31 )) + ( dateList >= DATE( 2024, 8, 11 ))), 2 ))
5 months out: =LET( dateList, EDATE(B5, SEQUENCE(12)), INDEX( FILTER( dateList, ( dateList <= DATE( 2024, 5, 31 )) + ( dateList >= DATE( 2024, 8, 11 ))), 5 ))