equivalent n´th day in a month

M

Mika

Hi ,


Appreciate if somebody can help with a formula to retrieve, given a
date, the equivalent one of the 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
 
B

Bob Phillips

Hi Mika,

How about this

=DATE(YEAR(A1)-1,MONTH(A1),1+7*(INT(DAY(A1)/7)--(MOD(DAY(A1),7)>0)))-WEEKDAY
(DATE(YEAR(A1)-1,MONTH(A1),8-WEEKDAY(A1)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

Hi ,


Appreciate if somebody can help with a formula to retrieve, given a
date, the equivalent one of the 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
 
M

Mika

Bob,

it seems to work only with this part:

DATE(YEAR(A1)-1,MONTH(A1),1+7*(INT(DAY(A1)/7)--(MOD(DAY(A1),7))

am I missing a particular case ????

Thanks for your help...
 
R

Ron Rosenfeld

Hi ,


Appreciate if somebody can help with a formula to retrieve, given a
date, the equivalent one of the 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
Click to expand...

What do you want for a result if there is no nth day in the particular month?

For example, consider 31 Jan 2005 which is the 5th Monday in January; in
January 2004 there were only 4 Mondays.


--ron
 
B

Bob Phillips

It is probably the length getting messed up in the post. Try this version

=DATE(YEAR(A1)-1,MONTH(A1),1+7*(INT(DAY(A1)/7)--(MOD(DAY(A1),7)>0)))-
WEEKDAY(DATE(YEAR(A1)-1,MONTH(A1),8-WEEKDAY(A1)))

and copy it into the formula bar not the cell

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
M

Mika

Hi Bob,

This is strange but your formula gives an error in the ")" of the
expression:
MOD(DAY(A1),7)>0))) if I delete one of this the formula is accepted but
the answer is wrong.

Bob, however if I use only:
=DATE(YEAR(A1)-1,MONTH(A1),1+7*(INT(DAY(A1)/7)--(MOD(DAY(A1),7))

works very well, I am testing it and so far so good !!. Thanks a lot.

Rgds
Mika
 
B

Bob Phillips

That's odd, I don't get that error, but the revised version you suggest is
missing closing brackets?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
R

Ron Rosenfeld

It is probably the length getting messed up in the post. Try this version

=DATE(YEAR(A1)-1,MONTH(A1),1+7*(INT(DAY(A1)/7)--(MOD(DAY(A1),7)>0)))-
WEEKDAY(DATE(YEAR(A1)-1,MONTH(A1),8-WEEKDAY(A1)))

and copy it into the formula bar not the cell
Click to expand...

Monday, January 31, 2005 --> Monday, February 02, 2004



--ron
 
B

Bob Phillips

The fourth Monday of January is the 4th Monday after the last day of the
previous December. So is the 5th, even if it doesn't fall in the same month.
If the OP doesn't want that, he needs to tell us what to do in that
circumstance, and it can be handled.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
M

Mika

Hi Bob,

I just typed the formula,not copy paste, and your formula worked like a
charm. My guess is that a hidden character was messing things up.

My reduced formula not always work, so thanks again.

Regarding the Jan 31, 2005, I think your formula is giving the right
answer for this application. I will check with the user. Anyway, you
solved my problem.

Thanks
Mika
 
R

Ron Rosenfeld

The fourth Monday of January is the 4th Monday after the last day of the
previous December. So is the 5th, even if it doesn't fall in the same month.

If the OP doesn't want that, he needs to tell us what to do in that
circumstance, and it can be handled.
Click to expand...

We both know it can be handled. The OP had been asked a few times what he
wanted to do in that situation, and had not responded. So I was hoping that he
would see this and clarify things.


--ron
 
Top