Hi ,

Appreciate if somebody can help with a formula to retrieve, given a
date, the equivalent one last year. An example:

today is Dec 19 th, 2005 which is the third monday of this month, then
my formula should give me: Dec 20 th, 2004 (the third monday of Dec.
last year)

I will use later this value in a vlookup construction to retrieve other
info. In this case I don´t want to use vba.

Rgds
Mika

Try a formula like the following:

=DATE(Yr,mon,1+((Nth-(dow=WEEKDAY(DATE(Yr,mon,1))))*7)+(dow-WEEKDAY(DATE(Yr,mon,1))))

Where Yr is the year, Mon is the month number, Nth is which day
you want (3 in your example, 3rd Monday), and Dow is the day of
week number (1 = Sunday, 7 = Saturday).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Mika" wrote in message
oups.com...
Hi ,

Appreciate if somebody can help with a formula to retrieve, given
a
date, the equivalent one last year. An example:

today is Dec 19 th, 2005 which is the third monday of this month,
then
my formula should give me: Dec 20 th, 2004 (the third monday of
Dec.
last year)

I will use later this value in a vlookup construction to retrieve
other
info. In this case I don´t want to use vba.

Rgds
Mika

Thanks a lot, that was quick Chip, and actually I read it from your
site but I can not figure out yet how to use for my case, as your
formula requires that you imput if what day of the week is (moday,
friday.etc). I am going to use it in a range with different dates...


Mika

Something like this?

=A1-52*7

Where the date is in cell A1. This number is 52 weeks back from the target
date.
--
HTH...

Jim Thomlinson


"Mika" wrote:

Hi ,

Appreciate if somebody can help with a formula to retrieve, given a
date, the equivalent one last year. An example:

today is Dec 19 th, 2005 which is the third monday of this month, then
my formula should give me: Dec 20 th, 2004 (the third monday of Dec.
last year)

I will use later this value in a vlookup construction to retrieve other
info. In this case I don´t want to use vba.

Rgds
Mika

On Mon, 19 Dec 2005 14:56:02 -0800, "Jim Thomlinson"
wrote:

Something like this?

=A1-52*7

Where the date is in cell A1. This number is 52 weeks back from the target
date.

Won't work. Try for example 01/01/2004.

On 19 Dec 2005 14:42:38 -0800, "Mika" wrote:

Hi ,

Appreciate if somebody can help with a formula to retrieve, given a
date, the equivalent one last year. An example:

today is Dec 19 th, 2005 which is the third monday of this month, then
my formula should give me: Dec 20 th, 2004 (the third monday of Dec.
last year)

I will use later this value in a vlookup construction to retrieve other
info. In this case I don´t want to use vba.

Rgds
Mika

What do you expect to retrieve, when your start day is for eample the
fifth Saturday in a month (e.g., Jan 29 2005) but Jan 2006 has only 4
saturdays?

See response in public.excel

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Mika" wrote in message
oups.com...
Hi ,

Appreciate if somebody can help with a formula to retrieve, given a
date, the equivalent one last year. An example:

today is Dec 19 th, 2005 which is the third monday of this month, then
my formula should give me: Dec 20 th, 2004 (the third monday of Dec.
last year)

I will use later this value in a vlookup construction to retrieve other
info. In this case I don´t want to use vba.

Rgds
Mika