Date ranges

[Paul T]

Hi

I have a list of predefined dates in column B in colum C formula to show
todays date. (i.e. if today = 24-6-06)

What I want is D1 to to show B2 (19-7-06) and D2 to show B1 (12-6-06)

A B C D
1 1 12-6-06 =today() next date =
2 2 19-7-06 previous date =
3 3 24-8-06
4 4 30-9-06

Any help appreciated.

Regards

Paul T

 

[joeu2004]

I have a list of predefined dates in column B in colum C formula to show
todays date. (i.e. if today = 24-6-06)
What I want is D1 to to show B2 (19-7-06) and D2 to show B1 (12-6-06)
A B C D
1 1 12-6-06 =today() next date =
2 2 19-7-06 previous date =
3 3 24-8-06
4 4 30-9-06

Why doesn't the following obvious solution work for you:

D1: =B2
D2: =B1

I suspect there is something (a lot!) missing in the explanation of
what your truly want to do. For example, as I interpret your question,
what you have in column C is irrelevant.

PS: If you mean that you want to repeat that alternating pattern down
column D, cutting and pasting (or dragging the lower right corner of)
the pair will do the job.

 

[Paul T]

Sorry should of explained that D1 is based on date in C1 looking up nearest
match in Column B (likewise for D2)
E.g. Tried =VLOOKUP(C1,B2:B4,1) but no joy

 

[joeu2004]

Sorry should of explained that D1 is based on date in C1 looking up nearest
match in Column B (likewise for D2)
E.g. Tried =VLOOKUP(C1,B2:B4,1) but no joy

Let me try to restate what you want. You want to search column B for
the closest date on or after the date in C1. In D1, you want the
matched date; in D2, you want the date from the row before the matched
date. Is that about right?

There are some details to nail down.

If a date in column B exactly matches C1, do you truly want D1 to be
the matched date, as I stated; or do you want D1 to be the next date --
in which case, D2 would be the matched date?

And what if the matched date is B2, the first row of the table
searched? There is no "previous date" in that case for D2.

And what if C1 exactly matches the last date in column B? Depending on
your answer to the first question (about exact matches), there might be
no "next date" for D1.

The latter two boundary conditions can probably be handled by using
ISERROR() in an IF() function. But they should be covered in a
complete solution -- or you might assure us that column B will be
designed so that those conditions cannot happen, in deference to
getting a more tractable solution.

 

[joeu2004]

Sorry should of explained that D1 is based on date in C1 looking up nearest
match in Column B (likewise for D2)

Do you really mean "nearest"? That is, if the date in C1 is more than
half way between two dates in column B, choose the latter; but if it is
less than half way between the two dates, choose the former. (And who
knows what you want if C1 is exactly half way.) I hope that is not
what you mean.

I wrote as a WAG:

Let me try to restate what you want. You want to search column B for
the closest date on or after the date in C1. In D1, you want the
matched date; in D2, you want the date from the row before the matched
date. Is that about right?

Actually, it appears that the loopup functions are better suited for
doing this: search column B for the closest date on or __before__ the
date in C1. If that is what you want, then:

D1: =offset(B1, lookup(C1, B1:B4, A1:A4), 0)
D2: =vlookup(C1, B1:B4, 1)

That relies on the fact that in column A, you put the relative row
numbers of the table entries in column B. Even if that does not apply
directly to your problem statement (which is still ill-defined, IMHO),
hopefully it gives you the tools for crafting your own solution.

I also wrote earlier:

And what if the matched date is B2, the first row of the table
searched? There is no "previous date" in that case for D2.

"B2" was a typo. It should be B1.

The latter two boundary conditions can probably be handled by using
ISERROR() in an IF() function. But they should be covered in a
complete solution -- or you might assure us that column B will be
designed so that those conditions cannot happen, in deference to
getting a more tractable solution

It would be better to design the table in column B so that the boundary
conditions never arise, assuming valid input in C1. That is, for the
"on or before" search that I speculated, column B should contain a date
that is equal to or before any valid date that might be in C1, and
column B should contain a date that is after any valid date that might
go into C1. Alternatively, you could write the following, for example:


D2: =if(iserror(vlookup(C1, B1:B4, 1)), "", vlookup(C1, B1:B4, 1))

But the similar formula for D1 becomes __very__ messy because you must
account for the last date in column B. As I said, simply design table
B with appropriate values to avoid the boundary conditions. They can
be hidden rows, if you like.


-----

 

[Paul T]

Hi

You have cracked it with the Offset method!

I am not worried about a match date as what I intend to do is either prior
or after =today(). So I will always have a prior and after scenario.

I am very grateful for you guidence and assistance on this matter.

Thank you.

Paul T

 

[joeu2004]

You have cracked it with the Offset method!

Glad it works for you. One improvement....

D1: =offset(B1, lookup(C1, B1:B4, A1:A4), 0)
[....]
That relies on the fact that in column A, you put the relative row
numbers of the table entries in column B.

No need to rely on column A. You could do:

D1: =offset(B1, match(C1, B1:B4), 0)