Returning a previous date & value based on the current date

  • Thread starter Thread starter Hawkster
  • Start date Start date
H

Hawkster

Hello,

Sorry not sure if the subject describes clearly what i am after.

I have a report which is setup as follows (with data going back a few
years):

Week Beginning Value
18/12/06 43
25/12/06 25
1/1/07 9
8/1/07 36
15/1/07 98


I am after a formula that will return the date and value that is 52
weeks back from the current date.

This report will constantly be growing so it would need to be a rolling
formula.

Any help would be appreciated.

Neil
 
=TODAY()+(52*7)

if above is in H1, then the value is

=INDEX(B:B,MATCH(H1,A:A,0))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
Hi Bob,

the Today formula returns a date in the future so i made a change to
the formula as

=Today()-(52*7)

In both methods however they return the actual day 365 days in either
direction. The table the index formula, will look up won't be able to
find a match because the dates are all the first days of each week. I
tested it just to be sure and it returns an #N/A error.


Neil
 
TODAY() is not a date in the future,it is today, the date of the current
day, whatever way you want to put it it is today.

Your dates, when you say start of the week, what day is that, the Monday or
the Sunday. Is the data all in ascending date order.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
Back
Top