Return a value when Date x is between Date y and z

Hi,

I have a table something like this:

            A                        B                 C
1       From                To	               Result
2       01/07/2009	01/07/2010	0-1
3       01/07/2010	01/07/2011	1-2
4       01/07/2011	01/07/2012	2-3
5       01/07/2012	01/07/2013	3-4

In another column (X) I have a list of Dates and I want return the
"result" from the row where the date lies between. So for example in
X3 I have 16/10/2011 and I can see this date is between the 2 dates in
row 4 in the table above so I want to return "2-3"

I've tried an array formula something like {=IF(AND
(X3>A2:A17,X3<A2:A17),C2:C17,"None")} but this doesn't do the trick.

Any suggestions welcome

Thanks

...pc
0
paulc05 (2)
7/30/2009 8:32:24 AM
excel 39879 articles. 2 followers. Follow

1 Replies
505 Views

Similar Articles

[PageSpeed] 19

Experiment with =VLOOKUP(X3,$A$2:$I$5,3,TRUE)
I think this does what you want
best wishes
-- 
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"PMC1" <paulc05@gmail.com> wrote in message 
news:c1bb87fa-2e3a-4ee7-80f0-2ac1e2ba3a17@g1g2000pra.googlegroups.com...
> Hi,
>
> I have a table something like this:
>
>            A                        B                 C
> 1       From                To                Result
> 2       01/07/2009 01/07/2010 0-1
> 3       01/07/2010 01/07/2011 1-2
> 4       01/07/2011 01/07/2012 2-3
> 5       01/07/2012 01/07/2013 3-4
>
> In another column (X) I have a list of Dates and I want return the
> "result" from the row where the date lies between. So for example in
> X3 I have 16/10/2011 and I can see this date is between the 2 dates in
> row 4 in the table above so I want to return "2-3"
>
> I've tried an array formula something like {=IF(AND
> (X3>A2:A17,X3<A2:A17),C2:C17,"None")} but this doesn't do the trick.
>
> Any suggestions welcome
>
> Thanks
>
> ..pc 


0
bliengme (657)
7/30/2009 2:21:18 PM
Reply:

Similar Artilces:

How to link Excel deadline dates to Outlook?
Need to link dates to Outlook users in either email or tasks, for purpose of task reminders and to track completion of task. ...

How to set the value WINCEROOT in platform builder for mobile 5.0
I installed the platform builder for windows mobile 5.0. and chose the Platform builder and AKU, but after install, the first time i ran the platform builder, it shown "not a valid build tree path", and i can't create new OS design by IDE mode, so could someone help me ? Try Tools\Options\Platform Builder for CE -- Bruce Eitman (eMVP) Senior Engineer Bruce.Eitman AT Eurotech DOT com My BLOG http://geekswithblogs.net/bruceeitman Eurotech Inc. www.Eurotech.com "Lin, Liberty" <Lin, Liberty@discussions.microsoft.com> wrote in message news:4D2C...

Any way to force European format dates in sheets?
Is there a way to configure Excel (Office 2000) to always display dates in Euro format instead of US? It currently accepts entries in Euro format (dd/mm/yy) but always displays them in sheets in US format (mm/dd/yy), which is kinda confusing. I can't see a configure option covering this - am I missing something? Cheers, -Neil F. Suspect this is down to your PCs settings - check control panel - Regional Options. Are you sure its accepting Euro dates, and not just those that can be mis-interpreted as US and leaving others just as text? Thanks for that, Ben. I've checked t...

How do I specify a constant Y-value for an X-series
I am using an XY (scatter) chart to plot eight time-stamped events (series). I want the Y-value for each event to be a constant so I can use different colored error bars with different heights to show the event/series on my chart. Currently I have filled columns with the desired constant value (4, 6, 8, 10, ...) for each X-series and use these columns to supply the constant Y-value. There must be a simpler way to accomplish my objective. If I provide the constant as, for example, 6, to the Y-series, it uses this value only for the first x-series point. I want it to use this value ...

Pivot Page field filter based on cell value
Hi, I am new in VBA and I have created a code that gets information from an access query and set up a pivot table. The worksheet contains multiple pivottables. All pivot tables have at least one page field (product). The worksheet should be copied and then every pivot on the active sheet should be filtered on one product (example: worksheet for milk, worksheet for cheese, worksheet for etc). This for every product (couple op 30-35 products). How can I copy the 'mastersheet' (named ' Master') and filter all pivot tables for a specific Page field product? Hi, Select the pivot ...

Y axis, need varying units
I'm using Excel 2002. I have data that looks like this: X Y 2.56937391 4.077622647 2.58546073 8.180775241 2.589949601 12.29105172 2.657055853 16.50782651 2.741151599 20.8580621 2.854306042 25.38787495 2.862131379 29.9301067 2.935507266 34.58878672 3.192009593 39.65453856 3.250175948 44.812601 3.266936911 49.99726325 3.268343914 55.18415844 3.339451441 60.483902 3.354684554 65.80782066 3.454234896 71.28972673 3.465234095 76.78908865 3.532754379 82.39560594 3.572755465 88.06560537 3.582631439 93.75127807 3.937417581 100 I need to produce a scatter plot in excel. Simple to do in g...

Report critera
I have an unbound form that is used to set the criteria for a report. I have date fields for a start date and an end date. I want the report to return all records when the date fields are empty and cannot get it to work in VBA. I have it working when the date fields are populated, but when they are emty I get the error "Invalid Use of Null" I have tried a conditional statement to test for Null in both fields, but I get the same results. Seems like it should be straight forward, but I am probably looking at it too hard. Thanks The controls are used to set the criteria for...

delete cells with "x" in them
I want to delete all cells in a range that have "x" in them. I have used the following code: For Each Cell In Sheets("CDPTS").Range("B1:B363") If Cell.Value = "x" Then Cell.Delete Shift:=xlUp End If Next Cell However, this doesn't delete them all? I repeated the code several times and it finally does get them all. Is there a more reliable code to do my job? SDC scrabtree, The reason it's not working every time is that If cell B1 contains an x, cell B2 contains an x and B3 contains "qwerty&...

Entering dates in Excel 2002
Is it possible to enter and use dates prior to January 1,1900, in calucaltaions in Excel 2002. Specifically trying to calculate the number of days between two dates where one or both of the dates are prior to January 1, 1900. If so how? On Sat, 8 Jan 2005 21:27:01 -0800, "DickG" <DickG@discussions.microsoft.com> wrote: >Is it possible to enter and use dates prior to January 1,1900, in >calucaltaions in Excel 2002. Specifically trying to calculate the number of >days between two dates where one or both of the dates are prior to January 1, >1900. If so h...

Formatting Text to Numbers for Dates
I'm currently working with a column of dates. Some of the dates are text and some are numbers. The dates are in the following format 21/6/210 or 4/12/2013 (date/month/year). I used =isnumber() to verify cells. Half of the coulmn resulted in false values. I'm unable to format the dates to other formats, since some are text. Is there anyway to change the column so that all dates are numbers vs. text, thus allowing me to format them to other date formats??? Try running them through Data>Text to Columns with column data format set to date format of your choice. ...

Date formats #5
Hi, I am making a 12 month column report. I want the user to able to chose up to which month actaul data will be retrieved and going forward budgeted data. Can somebody help me with this? -- Paul P =MONTH(TODAY()) gets the month of today's date, and you can use that as your column offset -- HTH RP (remove nothere from the email address if mailing direct) "Penco Excel User" <PencoExcelUser@discussions.microsoft.com> wrote in message news:847B202F-816D-4DAB-88F4-0074BBBBA7AE@microsoft.com... > Hi, > > I am making a 12 month column report. I want the user...

I need week number in excell from a date, first week must be mini.
I need to get the week number in excell from a cell with a date (dd/mm/aaaa), but the first week of the year has 3 possiblilities, first week, with day nº 1, first week with at least 4 days of the new year or finally the first full week. How to do this in excell ? I tried with weeknum() but I can't choose the first week Thanks. Hi jPeich See Chip Pearson's site http://www.rondebruin.nl/weeknumber.htm And also this week calendar file http://www.rondebruin.nl/weeknumber.htm Regards Ron de Bruin http://www.rondebruin.nl "jPeich" <jPeich@discussions.mi...

LDAP Returned the error [20] or [34] (EventID: 8270)
We're currently having a problem in Exchange Server 2003. We've been getting these event log messages for quite a while now. We don't see any adverse affect but would like to stop these messages from coming in. What is the cause of these messages, should we be concerned? I've run DomainPrep again on our Root Domain Controller and even rebuilt some Recipient Update Services with no success. Thanks! Here is an example of the Event Log ([20] Error): LDAP returned the error [20] No Such Object when importing the transaction dn: <GUID=33B24F0D-CF67-4BD4-8BD3-8BF95AA...

Blank Cell if next cell value is 0
Hello: Please help me with this one: I like to have these cells: A1: Blue A2: 0 A3: Red A4: 1 A5: Green A6: 0 A7: Yellow A8: 7 convert to these: A1: A2: 0 A3: Red A4: 1 A5: A6: 0 A7: Yellow A8: 7 (if A2 value is 0 (zero), A1 should be blank and so on... Thanks in advance. Dervish Hi do you want a macro solution for this? or would be an output an an adjancent column be sufficient? -- Regards Frank Kabel Frankfurt, Germany "Dervish" <dervishme@comcast.net> schrieb im Newsbeitrag news:400aa860.0410281053.2aa1e388@posting.google.com... > Hello: > > Please help m...

Formula for counting specific cells with value greater than 0
I am looking for a formula that will allow me to count specific cells (not a range) that would hold a value greater than 1. If the cell had a value of less than one, it would not be included in the count. A5 = 1.1 A10 = 1.4 A15 = .75 A20 = 2.3 total count would be 3 Look at your earlier post and for possible answer there -- Regards, Peo Sjoblom "mmock" <mmock@discussions.microsoft.com> wrote in message news:B5CDFB7F-D31A-409B-A3DA-D49155EC3CDF@microsoft.com... >I am looking for a formula that will allow me to count specific cells (not >a > range) that would...

Minimize to tray on clicking X
Is there a way to minimize Outlook 2007 by clicking on the X button? you mean "close" it but still have it notify you of new mail? No, but there are 3rd party applications that do that. "John" wrote: > Is there a way to minimize Outlook 2007 by clicking on the X button? > . > In article <BA7B88A4-741E-4409-AFB3-0E35E41482E1@microsoft.com>, dlw@discussions.microsoft.com says... > > you mean "close" it but still have it notify you of new mail? No, but there > are 3rd party applications that do that. > > &quo...

Printing Charts with Two Y-Axes
I have several (more than 15) charts that have two axes - One for Qty and the Other for $$. When I choose print workbook, the charts all come out funny. Theonly way I can get them to print ok is to preview each chart and print separately. Does anyone know what I may be doing wrong? Thanks ...

Automatically enter current date in cell.
Hello, I have a column in my spreadsheet, which should always have current date mentioned in it. When a user adds a new row to a spreadsheet, I want current-date (MM/DD/YYYY) to be automatically entered in the cell, just by tabbing over the cell. In general, how can we specify default values for a column (i mean, values which EXCEL will automatically generate if user chooses not to update the cell) Thanks for the help. take a look at http://www.mcgimpsey.com/excel/timestamp.html In article <de8e098d.0408160539.2eed5558@posting.google.com>, mail_ssb@yahoo.com (ssb) wrote: >...

GP 10 release date
Can comeone please confirm the release date of GP 10? Thanks Bill June 18th, 2007 -- Richard L. Whaley Author / Consultant / MVP Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "Bill Nguyen" wrote: > Can comeone please confirm the release date of GP 10? > > Thanks > > Bill > > > 10-10-2007 final release. as Microsoft has say. "Bill Nguyen" wrote: > Can comeone please confirm the release date of GP 10? > > Thanks > > Bill > ...

CListCtrl::SetItem(LVITEM) returns FALSE
Hi, I can't figure out what is wrong with the code below. Everything seems to work fine if I simply ignore the return value from SetItem(). Does anyone have any suggestions? I've looked into sample code but it looks like nobody bothers to check the return value. Thanks, Bogdan LV_ITEM lvi = {0}; int iItem = 0; lvi.mask = LVIF_TEXT | LVIF_PARAM; // pszText and lParam are valid lvi.pszText = LPSTR_TEXTCALLBACK; lvi.iItem = iItem; lvi.iSubItem = 0; lvi.lParam = reinterpret_cast<LPARAM>(pContact); VERIFY(iItem == m_wndList.InsertItem(&lvi)); lvi.mask = LVIF_TEXT | LVIF_PA...

EXCEL : Replace a value in a cell by a picture
How can I replace a value in a cell , by a picture . Example : Replace the value 1 with a picture of a boat and replace value2 with a picture of a train take a look here: http://www.mcgimpsey.com/excel/lookuppics.html In article <272ABD6C-EFD7-4185-BA2C-62750A61EB85@microsoft.com>, Vanderzeypen <Vanderzeypen@discussions.microsoft.com> wrote: > How can I replace a value in a cell , by a picture . > Example : > Replace the value 1 with a picture of a boat and replace value2 with a > picture of a train ...

Inventory dates
If I am to add all the shipments for all different equipment types by receipient and other criteria, eg =SUMIFS(RECORDS!TOTALS,RECORDS!MONTH,$A$2,RECORDS!EQUIPMENTTYPE,C$2,RECORDS!RECIPIENT,$A4) This formula is validating the receipient, the equipment type and even the month it was shipped. Adding totals of all shipment by item description. Our inventory starts on the 22nd of each month and ends on the 21st of the following month. Up to this point, I had to add a column to specify what month's inventory the shipment must be applicable to, otherwise is considering the month to s...

Adding Date/Time fields
12/31/05 - 08 Hrs 10 Mins 01/01/06 - 01 Hrs 00 Mins How do I add these two fields ? I don't need the date, just adding the times. -- teastman ------------------------------------------------------------------------ teastman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30030 View this thread: http://www.excelforum.com/showthread.php?threadid=497168 Assume they are in A1:A2 and the format is always the same with spaces, Hrs, Mins and - =SUMPRODUCT(--SUBSTITUTE(SUBSTITUTE(MID(A1:A2,FIND("-",A1:A2)+2,255),"Mins",""),&qu...

Value between 2 numbers
I want to compare a number in a cell to a min and max number. If it doesn't fall between them it is bad if it does it is good. Example: 2.5 is Min. 5 is Max. Value is <2.5 or > 5 is "Bad" 2.5 is "Good", 5 is "Good" Any help is appreciated? One way: =IF(OR(A1<2.5,A1>5),"Bad","Good") -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "olman" <olman@discussions.microsoft.com> wrote in ...

Office v. X updates
During my installation of Office v. X 10.1.2 update, the message "An error prevented the update from completing 11002;2,-14" appeared, I could not proceed. Does anyone have suggestions what I should do? Thanks. On 2/13/04 1:22 PM, in article f8eb01c3f277$7e0da7c0$a301280a@phx.gbl, "zj" <anonymous@discussions.microsoft.com> wrote: > During my installation of Office v. X 10.1.2 update, the > message "An error prevented the update from completing > 11002;2,-14" appeared, I could not proceed. Does anyone > have suggestions what I should do? A...