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!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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
Back
Top