First Monday
 

If Jan/02/06 is the first Monday of the month how can I determine the date of
the first Monday in Feb, March, Apr. and so on. I would also like to be able
to determine the date for the 2nd Thursday of each month.

Thanks
Charlie

With help from Chip Pearson (http://www.cpearson.com/excel/DateTimeWS.htm)

Enter any date during the month in question in cell A1:

=DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKDAY(DATE(YE AR(A1),MONTH(A1),1)),1,0,6,5,4,3,2)

gives you the first Monday.

=DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKDAY(DATE(YE AR(A1),MONTH(A1),1)),4,3,2,1,0,6,5)+7

Gives you the second Thursday.

Hope this helps
Rowan

"Charlie O'Neill" wrote:

If Jan/02/06 is the first Monday of the month how can I determine the date of
the first Monday in Feb, March, Apr. and so on. I would also like to be able
to determine the date for the 2nd Thursday of each month.

Thanks
Charlie

Enter your date 2-jan-06 in A1,
In B1, enter: =WEEKDAY(A1)
A2:A12 enter 2, 3, 4... and so on for each month
B2: =DATE(YEAR($A$1),A2,1)
C2: =B2-WEEKDAY(B2)+$B$1+IF(WEEKDAY(B2)$B$1,7)
and drag down for all the first mondays in each month


for 2nd tuesday
=B2-WEEKDAY(B2)+$B$1+1+IF(WEEKDAY(B2)$B$1+1,14)

Alternatively, you can simply change the date in A1 to get a tuesday and in
the cell C2 change 7 to 14 to get 2nd tuesdays.


Mangesh




"Charlie O'Neill" wrote in message
...
If Jan/02/06 is the first Monday of the month how can I determine the date
of
the first Monday in Feb, March, Apr. and so on. I would also like to be
able
to determine the date for the 2nd Thursday of each month.

Thanks
Charlie

Stolen from Daniel Maher

=A1+7-WEEKDAY(A1+5)

where A1 holds the 1st date of any month so if A1 holds 01/02/2006 and in A2
you want the first Monday in February and A3 March and so on

=DATE(YEAR(A1),MONTH(A1)+1,1)+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1)+5)

in A2 then copy down

for 2nd Thursdays

=A1+14-WEEKDAY(A1+2)

--
Regards,

Peo Sjoblom

(No private emails please)


"Charlie O'Neill" wrote in message
...
If Jan/02/06 is the first Monday of the month how can I determine the date
of
the first Monday in Feb, March, Apr. and so on. I would also like to be
able
to determine the date for the 2nd Thursday of each month.

Thanks
Charlie

Generic formula

=DATE(YEAR(A1),MONTH(A1),1+7*Nth)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-DoW))

Nth is the instance, 1st,.2nd etc, DoW is the serial number of the Day,
Sun=1, Mon=2, etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Charlie O'Neill" wrote in message
...
If Jan/02/06 is the first Monday of the month how can I determine the date
of
the first Monday in Feb, March, Apr. and so on. I would also like to be
able
to determine the date for the 2nd Thursday of each month.

Thanks
Charlie

Nth Day of the Month Formula, (aka, "First Monday")
 

"Bob Phillips" wrote:

Generic formula

=DATE(YEAR(A1),MONTH(A1),1+7*Nth)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-DoW))

Nth is the instance, 1st,.2nd etc, DoW is the serial number of the Day,
Sun=1, Mon=2, etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Charlie O'Neill" wrote in message
...
If Jan/02/06 is the first Monday of the month how can I determine the date
of
the first Monday in Feb, March, Apr. and so on. I would also like to be
able
to determine the date for the 2nd Thursday of each month.

Thanks
Charlie

Generic formula above did not work for me. I received the error NAME#?.

DOUG in Wichita

Nth Day of the Month Formula, (aka, "First Monday")
 

On Thu, 15 Apr 2010 08:26:01 -0700, DOUG
wrote:

Generic formula above did not work for me. I received the error NAME#?.

DOUG in Wichita

Then one of your NAME's, either in the formula or in a precedent, is not
properly defined. Possibly it's DOW, but without more information, everything
is speculation.
--ron

Nth Day of the Month Formula, (aka, "First Monday")
 

Just to add to Ron's post...

You did actually change the Nth to a real number and the DoW to a real number,
right?

And are you using an English language version of excel?

And are you in A1 reference style or R1C1 reference style? Do you see letters
at the top of each column or numbers???



DOUG wrote:

"Bob Phillips" wrote:

Generic formula

=DATE(YEAR(A1),MONTH(A1),1+7*Nth)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-DoW))

Nth is the instance, 1st,.2nd etc, DoW is the serial number of the Day,
Sun=1, Mon=2, etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Charlie O'Neill" wrote in message
...
If Jan/02/06 is the first Monday of the month how can I determine the date
of
the first Monday in Feb, March, Apr. and so on. I would also like to be
able
to determine the date for the 2nd Thursday of each month.

Thanks
Charlie

Generic formula above did not work for me. I received the error NAME#?.

DOUG in Wichita



--

Dave Peterson

Nth Day of the Month Formula, (aka, "First Monday")
 

ps. You may want to post the actual formula that you used, too.

Dave Peterson wrote:

Just to add to Ron's post...

You did actually change the Nth to a real number and the DoW to a real number,
right?

And are you using an English language version of excel?

And are you in A1 reference style or R1C1 reference style? Do you see letters
at the top of each column or numbers???

DOUG wrote:

"Bob Phillips" wrote:

Generic formula

=DATE(YEAR(A1),MONTH(A1),1+7*Nth)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-DoW))

Nth is the instance, 1st,.2nd etc, DoW is the serial number of the Day,
Sun=1, Mon=2, etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Charlie O'Neill" wrote in message
...
If Jan/02/06 is the first Monday of the month how can I determine the date
of
the first Monday in Feb, March, Apr. and so on. I would also like to be
able
to determine the date for the 2nd Thursday of each month.

Thanks
Charlie

Generic formula above did not work for me. I received the error NAME#?.

DOUG in Wichita



--

Dave Peterson

--

Dave Peterson