lookup a date from an array of date ranges if conditions are met

Please help...i need to lookup up a value on a specific date range.
for example:



column A:         column b
(from
9/01/2003


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

0
11/7/2003 2:09:50 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
219 Views

Similar Articles

[PageSpeed] 13

Sounds like autofilter could be something, select the data (should have
headers), do data>filter>autofilter,
filter on the date column using custom and gate than or equal to and put
09/01/03 as a criteria

-- 

Regards,

Peo Sjoblom


"nscanceran" <nscanceran.wj1qn@excelforum-nospam.com> wrote in message
news:nscanceran.wj1qn@excelforum-nospam.com...
>
> Please help...i need to lookup up a value on a specific date range.
> for example:
>
>
>
> column A:         column b
> (from
> 9/01/2003
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>


0
terre08 (1112)
11/7/2003 2:48:09 PM
Reply:

Similar Artilces:

Upcoming Bill Dates
When I select a bill that needs to be paid, rather then inserting the date that it needs to be paid, MS Money inserts a date that has long past. What do I need to do to fix this problem? Thanks Kirk It does not select the current date (today)? Mine does. >-----Original Message----- >When I select a bill that needs to be paid, rather then >inserting the date that it needs to be paid, MS Money >inserts a date that has long past. What do I need to do >to fix this problem? > >Thanks > >Kirk >. > Money gets its date information from the computer control...

Automaticaly put in the / in a date field
I want my users to input a date in the format dd/mm/yy I know some of them will mis out the separators and enter for example 140301 for the 14th March 2001, but Excel changes the date to something totally different. How can I create a mask (like in Access) so the separators are already displayed in the cell? For example the cell would look like / / and when the date is input it automatically jumps over the separator. You could use a worksheet event that changes the entry to a date. Chip Pearson has some code at: http://www.cpearson.com/excel/DateTimeEntry.htm Keith wrote: > ...

lookup and match
I have 2 worksheets. I have a main list that contains 1260 product codes and I need to lookup and match product codes from the second list. The main list contains a rebate price that I would like to show up next to each matching product code. Sounds simple enough but I've been stuck for 3days. Can anyone please help? I'm not sure which function to use and which steps to take for the formula. Hi Genietrapped I can definitely help you. However, there is not enough information in your e-mail to know what to tell you to solve your prolbme and it would be quicker for me ...

Date Entry in Excel 2000
Hi All, We recently upgraded to 2003 and one of our users says she was able to enter a date such as 10/24/04 in Excel 2000 as 102404 when she had the cell formatted as a date. I haven't been able to reproduce it on a 2000 machine nor does it make sense to me that it would work given that a date in Excel has a numeric value which is unrelated to the digits in the date. Is she mistaken or is there a setting I don't know about which allows this? Thanks Erin She's most probably mistaken. That behavior isn't native to XL. It's possible to do this with event macros...

Multiple time ranges
I am trying to create a formula that will look up the time values and return a letter. Times are in column F. Must include seconds (as values does include seconds). 07:00:00 - 17:30:00 = "A" 17:31:00 - 00:30:00 = "B" 00:31:00 - 06:59:00 = "C" <as values does include seconds).> But you miss out whole minutes in your specications. Anyway, this should work, but do check your threshold values. Maybe you need to add a second or less than a second. Set up a table like this (in this example: A1:B4) 0:00:00 B 0:30:00 C 7:00:00 A 17:30:00 B With your time to l...

How do get the years and days Between two dates
if you had 5/1/2006 and 2/28/2002 how would you get 4.17? If 5/1/2006 is in A1 and 2/28/2002 is in A2, then maybe: =(a1-a2)/365 or =(a1-a2)/365.25 But if you're trying to find differences between dates, you may want to take a look at =datedif(). You can find lots of info at Chip Pearson's site: http://www.cpearson.com/excel/datedif.htm (=datedif() was only documented in xl2k's help.) jhon doe wrote: > > if you had 5/1/2006 and 2/28/2002 how would you get 4.17? -- Dave Peterson ...

how to define range names
how do name cells so that I can see what the formula is calculating? Such as A1 * B1 = Final Price where A1 is price and B1 is discount so it reads in the final price cell as price * discount thanks in advance, A One quick way is to use the namebox (the droplist box just to the left of the "=" sign) In Sheet1, say: Select A1, then click inside the namebox, and type the name: price, then press ENTER Repeat to name cell B1 as: discount Then we could put in say C1: = price*discount The other (pe...

I need to get week start and week end dates to change automatically
I'm trying to get the week start and week end dates to chang automatically on a time sheet that I am working on. Anybody have formula? Joh -- Message posted from http://www.ExcelForum.com John, =INT(NOW()+2-WEEKDAY(NOW())) will return Monday's date, and =INT(NOW()+6-WEEKDAY(NOW())) will return Friday's date. Format as dates. HTH, Bernie MS Excel MVP "jlyons360 >" <<jlyons360.192pxt@excelforum-nospam.com> wrote in message news:jlyons360.192pxt@excelforum-nospam.com... > I'm trying to get the week start and week end dates to change > automa...

Formula to Calculate Dates
I need to enter one date, and have it compute the future date based on the number of years: For Example: - Date Added: June 2007 - Number Of Years: 5 Year - Expiration Date (Need Formula): June 2012 How would I get that result. BTW: Using Excel 2000 =DATE(YEAR(A1)+5,MONTH(A1),DAY(A1)) -- Kind regards, Niek Otten Microsoft MVP - Excel "S" <S@discussions.microsoft.com> wrote in message news:8504259F-4193-4CE3-9387-EAB68624076E@microsoft.com... |I need to enter one date, and have it compute the future date based on the | number of years: | | For Example: | - Date Added: J...

How to add the date after the "A1"
Hi, I type "The information gathered as of March 9, 2008" on cell "A1" Is there a way to make a date default like "The information gathered as of " & Date (default). Your help would be much apprecated. ="The informations gathered as of "&TEXT(TODAY(),"mmmm d, yyyy") -- Kind regards, Niek Otten Microsoft MVP - Excel <learning_codes@hotmail.com> wrote in message news:c11a1bfc-65ba-4cf3-bdef-c28bdcc19c1d@8g2000hse.googlegroups.com... | Hi, | | I type "The information gathered as of March 9, 2008" on cell "A1&q...

Target Date for Entourage Integration with Outlook
I recall reading something from Microsoft that a new Outlook is in the works that will replace the Entourage but can't remember the projection for when this event will take place? aRKay wrote: > I recall reading something from Microsoft that a new Outlook is in > the works that will replace the Entourage but can't remember the > projection for when this event will take place? According to the Mac Mojo blog, it will be released with the next version of Office for Mac, which is slated for release "Holiday 2010". We can probably assume this refers to t...

Re-format date (mm/dd/yyyy into dd/mm/yyyy)
Hi All, Could someone help me with the following: I have a range of dates which I want to convert from mm/dd/yyyy into dd/mm/yyyy, my formula (=MID(T1474,LEN(T1474)-6,2)&"/"&LEFT(T1474,2)&"/"&RIGHT(T1474,4)) works for most of the dates though in case of 1/12/2007 where the month only has one position (instead of 01) my formula takes "1/" for mm (instead of "01") Can one of you advice how to capture this issue in my (or a different) formula? Many thanks!!!! Rgds, Robert You could try: Data > Text to Columns > Next > Next >...

Excell & MailMerge dates
I have an excel file with dates broken down with a cell for the number in the month, a cell for the month, and a cell for the year. I used the "MMMM" for the month. In excell the cell reads "July", but when I use the cell as a data cell for mail merge in WORD I don't get the month I get the date number. Is there a way to get WORD to read the MONTH? Thanks From a previous post: You could use some helper cells and create strings for each field--then use those helper values: =text(a1,"dd-mmm-yyyy") =text(b1,"0.0") =text(c1,"#,##0.00&q...

Array formula
{=SUM((F45=10)*(H45={1,2,3})*{20,10,5})+((F45=20)*(H45={1, 2,3})*{42,21,11})} This formula works fine, up until the separated part, then it just does nothing(I put the spaces in just to clarify the problem area for this discussion). No matter what order put the arguments in, it only works up to the same point. Is this just a case of to much info? How do I extend this formula (I need it to be even longer the above example). thanks. Hi Atom, Try replacing SUM by SUMPRODUCT and do a normal completion with just ENTER since it is not an array formula Bernard "atom" <hmm@hmm.com...

Service Appointment, Activity History, and "Due Date"
When I create a service appointment in CRM, and I check the "All Day Event" check box, the schedule start and end times disappear, and the dates sync. This seems logical. However, when I look in the activity tab or the history tab the "Due Date" displays the next day. For example, I create a service appointment where the scheduled start date is 2/10/2007, and I select the "All Day Event" checkbox. The scheduled end date is set to 2/10/2007, the duration is set to 1 day, and the time fields disappear. If I view this service appointment in the activities ...

Adding if to Average If Array function
Hi, I recently had help understanding the following formula {=IF(ISERROR(AVERAGE(IF(Score>0,Score))),"",AVERAGE(IF(Score>0,Score)))} Thanks to Luke M, Pete UK and David Biddulph who provided the explanation. I've now been asked to add to this formula so that if either Q3 or Q4 is greater than 4 then the average cannot be lower than 3. Q1:Q35 is the named range score. This is really complicated because I still need to have an average and I'm thinking that the If part is non array but the average part still needs to be an array. Can you mix and match ...

Name, Date and Address Formats
Hi, I have a number of different spreadsheets that I need to impirt into a single database. Unfortunately some of the information is in different formats. Would anyone be able to provide me with a solution that can turn: A B Brown ABC Brown *A B C*Brown* Brown, A B Brown, Anthony Bernard Brown Anthony Bernard Mr A B Bernard Into separate fields for title, forename, initials & surname On a similar theme I also need to do much the same thing with addresses: 1 Victoria Road, Victoria, Victoriashire, VC1 1VC etc And lastly (for now anyway) I also have some dates that I need to ...

Compare this year/qtr/mo to date with prior year/qtr/mo to date
Hi, I'm trying to create a report that compares revenues received this year/qtr/mo/wk with revenues received last year in the comparable year/qtr/mo/wk, and additionally by sales division and sales manager. I have the data stored in a table with fields: SlsDivID, SlsMgrID, DtRecd and AmtRecd. I know I will need to use the date part function, but am not sure how to get the info in the report I need. format should be: SlsDiv - SlsMgr - CY YTD - LY YTD - G/L and % - CY QTD - LY QTD - G/L and % etc etc. I have tried pivot tables (not working) and various group and total functions (a...

Dates in Excel 2007 graph axis dialogs
I work for an environmental engineering firm, and commonly create time-series and other scatterplots of numerical data using Excel. In Excel 2003, I could format a time-series scatterplot axis minimum and maximum easily by typing in the dates in short date format. Not so in 2007 - it refuses to accept dates in anything but "days elapsed since 01/01/1900" format. Is there a workaround for this? Why was it changed? It was quite useful. Overall, I'm pretty unhappy with the new Excel. It seems to have acquired a lot more layers of very questionable usefulness. Many of...

USA to UK Dates
I have a list of data extracted from elsewhere and the dates are in USA format i.e. mm/dd/yyyy Can excel swap this around to UK format dd/mm/yyyy ? thanks in advance -- Vass ................................................ Now: YZF-R1, CBR1100xx-x Then: A100, MBX80, XL125, CB400, FZR600, CBR600Fv Hi, Format the dates to dd/mm/yyyy Highlight the dates Format > Cells > Number tab > Date > choose the one you need If it is not in there go to Custom and put dd/mm/yyyy in the space provided under Type. HTH. -- greg7468 -------------------------------------------------...

Evaluate a Range
I would like to create a formula that looks at a range with a specified criterion and when it finds the first entry in that range that meets that criterion returns the position of that data in the range. For example, given the following data beginning in A1 and extending to D1: 5,7 ,8,11 I would like to create a formula that looks for a value greater than 10 in that range and then returns the number 4 to signify that it is the fourth entry in that range. Thanks as always for your help. --- Message posted from http://www.ExcelForum.com/ One way: Array enter (CTRL-SHIFT-ENTER or CMD-RET...

Bug: printing of a specific page range is broken in MFC-VS2005
If you create an MFC application that includes printing support, and your application does print-time pagination as described in this MSDN library topic: http://msdn2.microsoft.com/en-us/library/w7wzay73(VS.80).aspx then specifying printing of a specific range of pages in the print dialog does not work. The application always prints all pages of the document. To see this problem download and build the WordPad sample MFC application. Start a new blank document, then paste into it enough text to fill several pages. Choose Print from the File menu. Choose a print range of page 1 to page 1....

How can I change the date range in a calendar object
I have a newsletter that I created in Pub 2003 with a calendar on one page. I was able to click on the calendar, then use the "change date range" to change the month. When I upgraded to Office 2007, that capability is gone. How can I change the calendar without deleting the object and reinserting it for the new month? You can't. Unfortunately the wizards are gone in all the Design Gallery objects. -- Mary Sauer http://msauer.mvps.org/ "Bob N" <BobN@discussions.microsoft.com> wrote in message news:7893B67C-9B2D-4B79-B59C-244E7B0D274C@microsoft.com... >I...

lookup #2
learnt lots from these pages and now have formulas doing all my results, but i can't find a way to auto my entries. i have 900 students with a larges number entering say the athletics. column A first; B surname; C house; D grade; E gender; F 100m to Q discus. i put a 1 in the events they enter. i then filter say junior girls discus and copy to appropriate form on the discus sheet. its time consuming and the whole process has to be redone if i have to accept late entries. i have tried vlookup with if. index and row but i can't get anything to work. is it possible say from the jun...

No date and time displayed
I am having a problem with the outlook. There are no date and time displayed on all my emails. When I received an email I should see date and time in the Received colume, but it's not there. Also, when I was trying to use the Calendar, date and time are not there too. I have updated my Outlook 2002 with SP-2. Can any one help? Thank you John ...