if the date ranges are in numerical order,
and there is no "overlap", then you can use a simple vlookup.
=VLOOKUP($H1,A1:C4,3,TRUE)
however, your dates aren't consecutive, meaning that they have blanks
(no date between march 8, 2005 and April 2, 2005)
so 3/15/2005 would show up as Alpha..
so, I'd suggest VBA:
Option Explicit
Public Function ChkDate(DateToCheck)
Dim R, rCnt
rCnt = Application.WorksheetFunction.CountA(Range("A1:A65000"))
For R = 1 To rCnt
If ((Cells(R, 1).Value < DateToCheck.Value) And (DateToCheck.Value <
Cells(R, 2).Value)) Then
ChkDate = Cells(R, 3).Value
Exit For
End If
Next R
If R > rCnt Then ChkDate = "Not found in ranges"
End Function
and use the formula:
=chkdate(H1)
Paul
----- Original Message ----
> From: Financeguy <velocity...@gmail.com>
> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> Sent: Mon, September 27, 2010 12:36:31 AM
> Subject: $$Excel-Macros$$ Date Interval Test
>
> I have a spreadsheet in which column A and B have date values....for
> instance A1 will have Jan 30, 2005 and B1 will be March 8, 2005.
> Corresponding to each range of dates in A and B, I have a value, lets
> say Alpha in Cell C1. These date ranges in A and B columns and code
> in C repeats for row 1 through 35.
>
> I would like to use a formula or vba to determine the intersection of
> the ranges. For instance if I have Feb 15, 2005 in H1 and various
> dates in the same column, Im trying to determine whether they fall
> within the interval for values in A1 and B1, A2 and B2, and so on to
> A35 and B35. The code would say true for Feb 15, 2005 to be between
> the range for A1 and B1, and the answer would be Alpha.
>
> A brief illustration follows.....
> A1 = Jan 20, 2005 B1 = March 8, 2005 C1 = Alpha
> A2 = Apr 2, 2005 B2 = June 12, 2005 C2 = Beta
> A3 = Jan 12, 2006 B3 = Feb 18, 2006 C3 = Gamma
> A4 = June 3, 2007 B4 = September 15, 2007 C4 = Epsilon
> .....
>
> If Value in H1 is Feb 15, 2005, then Alpha.....If Value in H2 is July
> 12, 2007, then Epsilon
>
> Any help is appreciated.
>
> Thanks,
> Vel
>
> --
>----------------------------------------------------------------------------------
>-
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
>http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
