Allocating based on Dates with IF/AND function

[Ollie7957]

Hi all

I am having real trouble with this formula and I wonder if someone can help please?

As per the attached, I am trying to get a 1 into every month which falls between the start and end dates, so for the first line of data, there should b a 1 in each month from June 2022 to May 2023, however I just cannot seem to get the formula right to calculate this! C2 is how far I have gotten so far, but I am getting total tunnel vision and can't get past this.

Any help would be greatly appreciated!

Many thanks

[AliGW]

Administrative Note:

Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[Ollie7957]

Already done, thank you

[AliGW]

In C2 copied across and down:

=IF(AND(EOMONTH($A2,-1)+1<=C$1,EOMONTH($B2,0)>=C$1),1,"")

[Fluff13]

How about

Please Login or Register  to view this content.

[Ollie7957]

Both formulas worked perfectly, thank you both very much!

[AliGW]

You're welcome.

[Fluff13]

Glad to help & thanks for the feedback.