First Monday

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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(YEAR(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(YEAR(A1),MONTH(A1),1)),4,3,2,1,0,6,5)+7

Gives you the second Thursday.

Hope this helps
Rowan
 
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
 
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)
 
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)
 
Bob Phillips said:
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)
Click to expand...
Generic formula above did not work for me. I received the error NAME#?.

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

DOUG in Wichita
Click to expand...

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
 
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???
 
Back
Top