Date Range Test

[Mashtonsmith]

Hello...

I'm trying to put together a formula that returns True or False based on entries within a linked range.

i.e. the example below shows a start and end date. The columns are tax years and based on the Start and End date each row should have a 1 in every tax year within that range.
The first example is True because every year covered by the range has an entry. The other 2 examples are False because they have an entry missing from one or more year/s within the range.

Each year runs from April 6th - April 5th

Start End 02/03 03/04 04/05 05/06 06/07 07/08 08/09

01/01/03 -10/06/08 1 1 1 1 1 1 1 TRUE
01/01/03 -10/06/08 1 1 1 1 1 1 FALSE
01/01/03 -10/06/08 1 1 1 1 1 1 FALSE
01/01/03 -10/06/08 1 1 1 1 1 FALSE

I can type ot how this should look but how to I turn this into a formula?

Thanks!

 

[GlennUK]

Have your tax year start and end as separate cells in 2 header rows, and have them as real dates, 6/Apr/2002 etc etc

Also have your start and end dates per row in separate cells.

It's then just simple ( ish ) logic tests. If you get stuck, I'll post an example formula.

 

[Mashtonsmith]

Hi Glen

The Start and End dates are in different cells - its just come out looking otherwise.

The format of the year columns is fixed so I cant change those.

 

[GlennUK]

Hi Glen

The Start and End dates are in different cells - its just come out looking otherwise.

The format of the year columns is fixed so I cant change those.

Are you saying that you cannot have extra rows with the appropriate dates in your worksheet?