Determining if a date falls between multiple sets of dates

MICHBROWNAOL

Board Regular
Joined
Mar 15, 2003
Messages
55
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?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,053
Office Version
  1. 365
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

Board Regular
Joined
Mar 15, 2003
Messages
55
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Board Regular
Joined
Mar 15, 2003
Messages
55

ADVERTISEMENT

There can be gaps between the ranges of dates
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,053
Office Version
  1. 365

ADVERTISEMENT

MICHBROWNAOL said:
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

Try:

=IF(LOOKUP(A2,D2:D10,E2:E10)>=A2,LOOKUP(A2,D2:D10,F2:F10),"No period")
Book1
ABCDEF
1DatePeriodStartEndPeriod
22005-03-27period 32005-01-012005-01-25period 1
32005-02-102005-02-28period 2
42005-03-222005-04-03period 3
52005-05-012005-05-07period 4
62005-06-102005-06-10period 5
Sheet3
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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.
 
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,168,117
Messages
5,857,482
Members
431,882
Latest member
saaaaaaaaaaaaaaaaaaaaaa

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