First monday of the month

  • Thread starter Thread starter Max
  • Start date Start date
Hi,


SELECT DateSerial( GivenYeart, GivenMonth, 1) as FirstDay,
FirstDay+Choose( DatePart("d", FirstDay), 1, 0, 6, 5, 4, 3, 2) As
FirstMonday
FROM ...



I assume a US week ordering (Sunday to Saturday). Change the constant values
inside the Choose function if appropriate: the second argument, here a 1,
is the number of days to add, 1, if "FirstDay" is a Sunday, etc.


Hoping it may help,
Vanderghast, Access MVP
 
Thanks for the reply...
I think this is exactly what I asked for...but this is not what I need.
After studying my desired output I am sure that what I need is a way to use
a date and show the weekday value and then show the sequence number of that
particular weekday. EG: 8-15-04 is a 1 (Sunday) and the sequence is 3 (the
third Sunday in August). Any Ideas.
Sorry about the misdirection, I don't want to waste anyone's time.
 
Hi,


No problem.

I'll use July 2004, August 2004 is just too ... simple (for a North American
calendar, first of August 2004 is a Sunday, so no "partial" week starting
the month, not typical, so, too simple). So, with July 2004.


If I want, about the 12th, get 2, since it is the second Monday of the
month,
1+ (Day(#7-12-2004#) -1) \ 7

or,
1+ (Day( x ) - 1) \ 7

If you want 3, because it is in the third line of the calendar:

1+ DateDiff("ww", #7-1-2004#, #7-12-2004#, vbSunday)


where the first date is the first of the month, and the second date is the
one we are inquiring.

( The first of the month can be obtain with: DateSerial( Year( x ),
Month( x ), 1 ), given a date, x.


Sure, if you just want to know if it is a Monday or a Friday:


DatePart("w", #7-1-2004#)

or


Weekday( #7-1-2004#)


Note that DatePart("ww", ... ) supplies the week number (of the year), but
don't make statistics based on it, since the last week of the year, 53, is
generally not a full week, and would have be to be merged with week=1 of the
year after (we know that stats are sensible to extremes, so having 2
"incomplete" sets (weeks 1 and 53) over 53 sets may lead to seriously wrong
"mean", "median", etc. )


Hoping it may help,
Vanderghast, Access MVP
 
Michel,
I use "1+ (Day(#7-12-2004#) -1) \ 7" this is exactly what I need...thak you
for your help. Simple code though, I am embarresed. I think I was trying to
make it harder than it had to be.
 
Back
Top