Date Calculation

[Guest]

I have searched for this answer to no avail, but what I am trying to do is enter a date (ie Tuesday March 30, 2004) into cell A1 and then in the consecutive rows input for example every last Tuesday in consecutive months (ie Tuesday April 27, 2004). I also would want to adjust this in a different worksheet to be possible every other Tuesday?

 

[Frank Kabel]

Hi
try
=DATE(YEAR(A1),MONTH(A1)+2,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+2,
0),2)-5))

--
Regards
Frank Kabel
Frankfurt, Germany

I have searched for this answer to no avail, but what I am trying to

do is enter a date (ie Tuesday March 30, 2004) into cell A1 and then in
the consecutive rows input for example every last Tuesday in
consecutive months (ie Tuesday April 27, 2004). I also would want to
adjust this in a different worksheet to be possible every other
Tuesday?

 

[Peo Sjoblom]

Assume you put your date in A1, since 03/30/04 is a Tuesday and then below
you want
then next last Tuesday in April, then in May and so on

In A2 put this formula and copy down

=DATE(YEAR(A1),MONTH(A1)+2,1)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+2,8-WEEKDAY(A1
)))

will return

4/27/2004
5/25/2004
6/29/2004
7/27/2004
8/31/2004
9/28/2004
10/26/2004
11/30/2004
12/28/2004

all last Tuesdays in this year
If you change the date in A1 to 03/31/04 it will return

4/28/2004
5/26/2004
6/30/2004
7/28/2004
8/25/2004
9/29/2004
10/27/2004
11/24/2004
12/29/2004

all last Wednesdays


--

Regards,

Peo Sjoblom

I have searched for this answer to no avail, but what I am trying to do is

enter a date (ie Tuesday March 30, 2004) into cell A1 and then in the
consecutive rows input for example every last Tuesday in consecutive months
(ie Tuesday April 27, 2004). I also would want to adjust this in a
different worksheet to be possible every other Tuesday?

 

[Frank Kabel]

Hi
sorry I misrea your post. I assumed you wanted the last workday for
each month. Take a look at the following site. There're many formulas
for getting the Nth day of a month for a specific weekday. In your case
I would calculate the first tuesday of the following month and then
subtracting 7 days to get the last tuesday of the current month:
http://www.cpearson.com/excel/DateTimeWS.htm

 

[Peo Sjoblom]

If you want every other week from a date in A1 just add 14 to the formula

=A1+14

copy down

 

[Peo Sjoblom]

You are welcome, thanks for the feedback

--

Regards,

Peo Sjoblom

Thanks PEO and Frank,

Both helped out tremendously. I figured out the every other week problem

after I posted. I wasn't thinking so easy. I guess I wanted some
complicated formula for this.