Fortnights between 2 specific dates

[Portuga]

Hi guys, it´s been a while and I could use some help.

I would like a formula that returns "1" if the fortnight in question is within 2 specific dates "0" if it´s not.

Please see the attached sheet for clarification.
Any pointers would be awesome

Thanks

[José Augusto]

Hi

Use in G2 the following formula and copy down and forward

Please Login or Register  to view this content.

[Portuga]

Obrigado José!

The formula works fine for the inputed values but I´ve encountered an issue when I change the dates.
For example if I change the "Out date" to "16/01/2018", there should be another "1" on the second January section, but it remains "0".

[Portuga]

This said, the formula will work fine for what I am looking for. My dataset will always start and end at the start/end of a fortnight an never in between so this solves my problem. Thanks again!

[FlameRetired]

Try array entering this in G2 filling down and across. (There has to be a simpler way, but I have not found one.)

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

[Phuocam]

Obrigado José!

The formula works fine for the inputed values but I´ve encountered an issue when I change the dates.
For example if I change the "Out date" to "16/01/2018", there should be another "1" on the second January section, but it remains "0".

Edit:
=IF(AND(INDEX($A$2:$A$49,2*COLUMNS($A$1:A$1)-1)>=$D2,INDEX($A$2:$A$49,2*COLUMNS($A$1:A$1)-1)<=$E2),1,0)

[Phuocam]

Or:

=(INDEX($A$2:$A$49,2*COLUMNS($A$1:A$1)-1)>=$D2)*(INDEX($A$2:$A$49,2*COLUMNS($A$1:A$1)-1)<=$E2)

[Portuga]

Try array entering this in G2 filling down and across

This is so cool! It identifies the values without looking at columns A:B

The only possible issue is when the "In Date" starts the year before the "Out Date".
Actually, it´s not an issue it just means that the formula looks at the "In Date" year as its base calculation.

Awesome

[Portuga]

Or:

=(INDEX($A$2:$A$49,2*COLUMNS($A$1:A$1)-1)>=$D2)*(INDEX($A$2:$A$49,2*COLUMNS($A$1:A$1)-1)<=$E2)

Works like a charm. Thank you!

[FlameRetired]

Thank you for the feedback, rep and marking your thread Solved.