Determining if a date falls between multiple sets of dates

[MICHBROWNAOL]

Is there an easy way to determine if a specific date falls between multiple sets of dates? I'm programming a spreadsheet for my company, and I need to determine if a date falls between a specific set of dates to determine if it falls in high, medium or low season. While this was easy originally with only three sets of dates per season, I now have to have 30 sets of dates available for each season per year (90 possibilities) times 3 years (now up to 270 possiblities). There is a potential of 1500 dates to be evaluated. My 4 meg spreadsheet has now increased to 41 megs by having to add all the additional calculations.

The formula I've used so far is:

=IF('Grp Accts'!$E8>='Grp Accts'!$AD$9,IF('Grp Accts'!$E8<='Grp Accts'!$AE$9,1,0),0)

where "Grp Accts"!$E8 is the date to be evaluated, and $AD$9 and $AE$9 are the starting and ending dates of the period. There are currently 270 formulas to evaluate each date...any thoughts on how to condense this and shrink my workbook size?

 

[Oaktree]

How about listing all 90 dates, then using the MATCH function?

=ISNUMBER(MATCH(A1,B1:B90,0)) will return true if your date is one of the listed dates and false if it is not.

 

[MICHBROWNAOL]

I like the idea, but the periods can be ranges of dates (i.e. Jan 1 to Feb 15, for example), so the match wouldn't work.

 

[barry houdini]

You could make the formula a tiny bit simpler

=('Grp Accts'!$E8>='Grp Accts'!$AD$9)*('Grp Accts'!$E8<='Grp Accts'!$AE$9)

Are the date ranges overlapping, or do they have gaps between or neither?

 

[MICHBROWNAOL]

There can be gaps between the ranges of dates

 

[MICHBROWNAOL]

but they are never overlapping

 

[Oaktree]

I like the idea, but the periods can be ranges of dates (i.e. Jan 1 to Feb 15, for example), so the match wouldn't work.

But, you could list each date from Jan 1 to Feb 15 individually...

 

[fairwinds]

Hi,

Try:

=IF(LOOKUP(A2,D2:D10,E2:E10)>=A2,LOOKUP(A2,D2:D10,F2:F10),"No period")

Book1
	A	B	C	D	E	F
1	Date	Period		Start	End	Period
2	2005-03-27	period 3		2005-01-01	2005-01-25	period 1
3				2005-02-10	2005-02-28	period 2
4				2005-03-22	2005-04-03	period 3
5				2005-05-01	2005-05-07	period 4
6				2005-06-10	2005-06-10	period 5
Sheet3

 

[MICHBROWNAOL]

I love the lookup concept, but it's not working for me.

MP PISCES 0805ea1.xls
	Y	Z	AA	AB	AC	AD	AE	AF
7	2005SeasonFactor	2006SeasonFactor	2007SeasonFactor		DemandPeriods:
8	0	0	0				2005
9	0	0	0		H	1/1/05	1/31/2005	1
10	0	0	0		H	4/1/2005	4/30/2005	1
11	0	0	0		H	7/1/2005	7/31/2005	1
12	0	0	0		H	10/1/2005	10/31/2005	1
13	0	0	0		H	1/0/1900	1/0/1900	1
Grp Accts

What am I doing wrong?

 

[fairwinds]

The list of dates must be sorted ascending.

Having zero in AD12:AE12 does not make it so.

Change formula in AE12 to

=IF(Dates!C12,Dates!C12,"")

to eliminate zero values.