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

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

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

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 -

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

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 -