EXCEL- Trying to count specific dates between two but also in specicif Years. Excluding the days before or after the dates asked

Question

I've been looking and searching for this for way too long. I made some dates and added some for tests which I will share.

I'm trying to count days between two dates, leaving it blank if a cell is empty, which I already found. The problem is I need to count the days by years and count the days for different cells for 365 days from today. For example the number of days in 2023 between those two 01/01/2023 to 12/31/2024 and then the number of days in 2024 between those two. Which of course will be in separate cells. And the number of days from all those dates ( C and D cells 4 to 8.) 365 days starting today.

The formula below works but only for one start date and end date but not all. I want to count all the days from all the departures and Arrivals with the 365 rule.

=IF(D4 < (TODAY() - 365), 0, IF(C4 >= TODAY() - 365, D4-C4, D4-(TODAY()-365)))

I actually need to count the amount of days each year for a bunch of cells but I think to be safe I can just add them after by doing a sum. As you can see we have dates from December to March which means some days should be counted in 2023 and others in 2024. But the other dates that are in 2023 should also be included in the 2023 total days. Hopefully, it's making sense.

**The =cell on the image is for the cumulative. If you want to understand this, it's to find out how many days from today back 365 days I've been in the US and how many days each year. I found online that two calculations need to be done unless it's changed. Also, I realized there is a facultative estimate date of March 2024 which I am now realizing might not even work to count days because of the rule but it's something I'd probably have to calculate a different way. Like counting the days left from today so there's no going over the maximum amount of days permitted.

Thank you in advance!enter image description here

I've tried countif with sum start date > than and end date < than in different ways, but I don't think I was doing those right. I used fomulas found online and tried to tweek it. Ya I've been on this one for too many hours.

Answer 1

Pls. try this formula with LET function (in cell D1)

Set the range A2:B3 (2x) to the actual data range.

Edited: to @Ron comments.

=LET(a,CHOOSECOLS(A2:B3,1),
     b,CHOOSECOLS(A2:B3,2),
     c,SEQUENCE(1,YEAR(MAX(b))-YEAR(MIN(a))+1,YEAR(MIN(a)),1),
     VSTACK(c, IF(c=YEAR(a),
                  DATE(YEAR(a),12,31)-a+1,
                  IF(c=YEAR(b),
                     b-DATE(YEAR(b),1,1)+1,
                     IF((c>YEAR(a))*(c<YEAR(b)),
                        DATE(c,12,31)-DATE(c,1,1)+1,
                        0
                     )
                  )
               )
     )
 )

RESULT