Returning a previous date & value based on the current date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hawkster - 23 Jan 2007 21:44 GMT

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

Reply to this Message

Bob Phillips - 23 Jan 2007 22:02 GMT

=TODAY()+(52*7)

if above is in H1, then the value is

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

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

> Hello,
>
[quoted text clipped - 19 lines]
>
> Neil

Reply to this Message

Neil - 23 Jan 2007 22:28 GMT

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()+(52*7)
>
[quoted text clipped - 33 lines]
> >
> > Neil

Reply to this Message

Bob Phillips - 24 Jan 2007 00:23 GMT

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.

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

> Hi Bob,
>
[quoted text clipped - 47 lines]
>> >
>> > Neil

Reply to this Message

Roger Govier - 24 Jan 2007 00:29 GMT

Hi Neil

Try
=(TODAY()-52*7)-WEEKDAY(TODAY()-52*7)+2

Signature

Regards

Roger Govier

> Hi Bob,
>
[quoted text clipped - 49 lines]
>> >
>> > Neil

Reply to this Message

Rate this thread: