Flag to get active (i.e., 1) if the month number is matched between two dates?

[yaswanth92]

Hi,

I have semi-annual timeline model with period ending September & March. My input is the month number. So, if that particular month number is present between the period start date and period end date, the result should be 1 otherwise 0.

Can you please help me with the formula? Please "NO" - NESTED IF function, OFFSET function or Array formula.

 

[GraH - Guido]

Assuming you have a date somewhere it could be = AND(MONTH(date) <10, MONTH(date) > 2)
Why don't you upload a sample file?

 

[yaswanth92]

Thanks, Grah. But, I am looking for a formula without using hardcoded values (0 or 1 can be used) inside. Please find attached the sample excel file.

 

[Peter Bartholomew]

Using CEILING to block months together in 6s
= CEILING( MONTH(date) - 3, 6 ) = 6

… or a variant of Guido's solution that allows 'date' to be an array
= ( MONTH(date) <10) * (MONTH(date) > 3)

which leads me to …
why no Array formulas?

I use them to simplify a solution that would otherwise be a mess of similar (but not necessarily identical) formulas.

 

[Peter Bartholomew]

To avoid hardcoded variables, define the constants
monthsPerPeriod = 6 and
periodOffset = 3
as Names or, at least, as labelled cells.

The latter approach is needed if you are to comply with some of the more primitive modelling standards.