Date Range Test

Mashtonsmith

Board Regular
Joined
Oct 1, 2004
Messages
164
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!
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,523
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

Board Regular
Joined
Oct 1, 2004
Messages
164
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,523
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,915
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top