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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,686
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,686
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,864
Messages
5,834,057
Members
430,260
Latest member
MANICX100

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