Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Range
Hello,
I have got a list of dates. For each date I want to show whether this is 0-34 days, 35-69 days, 70-104 days, 105 to 140 days or 140+ I have set up a table with the different ranges in and now need to look up to the table. Any ideas would be apreciated. Thank you. Beth |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Range
I assume you are comparing those dates (assumed to be in column A)
with today's date. If so, then set up your table like this: 0 0-34 days 35 35-69 days 70 70-104 days 105 105-139 days 140 140+ Suppose this occupies L1:M5, then put this formula in B1: =VLOOKUP(TODAY()-A1,L$1:M$5,2) and copy down. I've assumed that your dates are all older than today, but if they are dates in the future then you will need to change it to: =VLOOKUP(A1-TODAY(),L$1:M$5,2) Hope this helps. Pete On Dec 5, 4:10*pm, Beth wrote: Hello, I have got a list of dates. *For each date I want to show whether this is 0-34 days, 35-69 days, 70-104 days, 105 to 140 days or 140+ I have set up a table with the different ranges in and now need to look up to the table. Any ideas would be apreciated. *Thank you. Beth |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Range
Hi Everyone
I have done this with nested IF statements. If there is a better way to do this please let me know! Kind Regards, Beth "Beth" wrote: Hello, I have got a list of dates. For each date I want to show whether this is 0-34 days, 35-69 days, 70-104 days, 105 to 140 days or 140+ I have set up a table with the different ranges in and now need to look up to the table. Any ideas would be apreciated. Thank you. Beth |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Range
You probably didn't see my post while you were compiling your second
one. Pete On Dec 5, 4:32*pm, Beth wrote: Hi Everyone I have done this with nested IF statements. If there is a better way to do this please let me know! Kind Regards, Beth "Beth" wrote: Hello, I have got a list of dates. *For each date I want to show whether this is 0-34 days, 35-69 days, 70-104 days, 105 to 140 days or 140+ I have set up a table with the different ranges in and now need to look up to the table. Any ideas would be apreciated. *Thank you. Beth- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Range
Hi Pete
The dates are all different, so it will not be exactly 35 or 70 days. E.g the date may be 21 days ago! Nested IF statements have worked but they are not very elegant. VLookup would be much better if I can get it working. Thank you, Beth "Pete_UK" wrote: I assume you are comparing those dates (assumed to be in column A) with today's date. If so, then set up your table like this: 0 0-34 days 35 35-69 days 70 70-104 days 105 105-139 days 140 140+ Suppose this occupies L1:M5, then put this formula in B1: =VLOOKUP(TODAY()-A1,L$1:M$5,2) and copy down. I've assumed that your dates are all older than today, but if they are dates in the future then you will need to change it to: =VLOOKUP(A1-TODAY(),L$1:M$5,2) Hope this helps. Pete On Dec 5, 4:10 pm, Beth wrote: Hello, I have got a list of dates. For each date I want to show whether this is 0-34 days, 35-69 days, 70-104 days, 105 to 140 days or 140+ I have set up a table with the different ranges in and now need to look up to the table. Any ideas would be apreciated. Thank you. Beth |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Range
Well, just follow the example I gave you. Using VLOOKUP with only 3
parameters will cause it to look for the highest match which is less than or equal to the lookup value. So, if you have 21 days, it will match with 0 and return "0-34 days". If you have 57 days, this will match with 35 and return "35-69 days". The data has to be sorted (which it is). Hope this helps. Pete On Dec 5, 4:41*pm, Beth wrote: Hi Pete The dates are all different, so it will not be exactly 35 or 70 days. *E.g the date may be 21 days ago! Nested IF statements have worked but they are not very elegant. *VLookup would be much better if I can get it working. Thank you, Beth "Pete_UK" wrote: I assume you are comparing those dates (assumed to be in column A) with today's date. If so, then set up your table like this: * *0 * * 0-34 days * 35 * *35-69 days * 70 * *70-104 days 105 * *105-139 days 140 * *140+ Suppose this occupies L1:M5, then put this formula in B1: =VLOOKUP(TODAY()-A1,L$1:M$5,2) and copy down. I've assumed that your dates are all older than today, but if they are dates in the future then you will need to change it to: =VLOOKUP(A1-TODAY(),L$1:M$5,2) Hope this helps. Pete On Dec 5, 4:10 pm, Beth wrote: Hello, I have got a list of dates. *For each date I want to show whether this is 0-34 days, 35-69 days, 70-104 days, 105 to 140 days or 140+ I have set up a table with the different ranges in and now need to look up to the table. Any ideas would be apreciated. *Thank you. Beth- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need a formula to determine if date falls within a date range then | Excel Worksheet Functions | |||
Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions |