Trying to match number of days in a cycle to the month they fall into

Hi all,

I'm really struggling with this but I don't think it should actually be that hard - must be the silly season!

I have a 45 day cycle which has three components: A (7 days), B (21 days) and C (17 days). I want to know how many days of each cycle fall into each month over a 5 year period, but the IF statements I have been trying are getting extremely complicated / not really working.

I have attached an excel spreadsheet to better explain what I am trying to do.

Thanks so much

Cycle data.xlsx

write at H6
=SUMPRODUCT((MONTH(C$6:C$54)=MONTH($G6))*(YEAR(C$6:C$54)=YEAR($G6)))

copy down and right

thanks, that's super helpful.

one additional thing though, in some cases the cycle may start but not complete in the month - so for example in the first january there will only be 6 days of Cycle 3, not 17 days. Is there anyway to return days rather than whether the cycle starts?

write at H6
=SUMPRODUCT((MONTH(C$6:C$54)=MONTH($G6))*(YEAR(C$6:C$54)=YEAR($G6)))

copy down and right

Sorry if I wasn't clear - the formula is perfect to tell me whether or not a cycle commences in the month - but what formula can I use to see how many days of the cycle falls into the given month?

I've attached the spreadsheet to make my question more clear!

Thanks v much for your help

Cycle data 2.xlsx

On your original sheet, in cell H6, try:

=SUM((MONTH($C$6:$C$54)=MONTH($G6))*(YEAR($C$6:$C$54)=YEAR($G6))*IF(DATE(YEAR($G6),MONTH($G6)+1,1)-$C$6:$C$54>7,7,DATE(YEAR($G6),MONTH($G6)+1,1)-$C$6:$C$54))+SUM((MONTH($C$6:$C$54)<>MONTH($G6))*(MONTH($C$6:$C$54+6)=MONTH($G6))*(YEAR($C$6:$C$54+6)=YEAR($G6))*IF($C$6:$C$54+7-$G6>6,6,$C$6:$C$54+7-$G6))

Confirmed with Ctrl-Shift-Enter, not just enter. If that works then we can amend it to do cycles 2 and 3.

Yep, Andrew-R that works a dream on Cycle 1

Good stuff. So, in I6:

=SUM((MONTH($D$6:$D$54)=MONTH($G6))*(YEAR($D$6:$D$54)=YEAR($G6))*IF(DATE(YEAR($G6),MONTH($G6)+1,1)-$D$6:$D$54>21,21,DATE(YEAR($G6),MONTH($G6)+1,1)-$D$6:$D$54))+SUM((MONTH($D$6:$D$54)<>MONTH($G6))*(MONTH($D$6:$D$54+20)=MONTH($G6))*(YEAR($D$6:$D$54+20)=YEAR($G6))*IF($D$6:$D$54+21-$G6>20,20,$D$6:$D$54+21-$G6))

And in J6:

=SUM((MONTH($E$6:$E$54)=MONTH($G6))*(YEAR($E$6:$E$54)=YEAR($G6))*IF(DATE(YEAR($G6),MONTH($G6)+1,1)-$E$6:$E$54>17,17,DATE(YEAR($G6),MONTH($G6)+1,1)-$E$6:$E$54))+SUM((MONTH($E$6:$E$54)<>MONTH($G6))*(MONTH($E$6:$E$54+16)=MONTH($G6))*(YEAR($E$6:$E$54+16)=YEAR($G6))*IF($E$6:$E$54+17-$G6>16,16,$E$6:$E$54+17-$G6))

Again, confirmed with Ctrl-Shift-Enter and that should be it.

PS - It's not the silly season, this is a really hard problem

Brilliant!!! Thanks so much, I'd been working on this for ages and was getting nowhere ... and now i can't blame the silly season as i would never have gotten there by myself! Now just need to decipher for the next time!