First Monday of every month

[VisualBasicHelp]

Hi,

How can I find the first monday of every month.

With regards

 

[HarleyQuinn]

Every first month since 01/01/1900? The first Monday of the current month? Could you be a little more specific in your criteria please?

Regards

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[VisualBasicHelp]

Thanks Harley.

Actually, I would like to run a task everyday, from today, which will check whether , it is the first monday of that particular month,if yes then it has to send a file.

 

[HarleyQuinn]

Hmm, I'd gone off and devloped something that found the first Monday of every month for a given range, that'll teach me to wait for a reply...

Anyway, this code is a cut down version and should do something similar to what you're after:

Dim d As Date

d = DateSerial(Year(Date), Month(Date), 1)

If Date = DateAdd("d", (7 - Weekday(d, 1) + vbMonday) - IIf(Weekday(d, 1) <= vbMonday, 7, 0), d) Then

    'run task
    
End If

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[VisualBasicHelp]

Thanks Harley. This code is exactly what I actually needed.

Many Thanks

 

[HarleyQuinn]

Glad I could help

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[strongm]

>I'd gone off and devloped something that found the first Monday of every month for a given

Oh, HQ - you should have just done a quick keyword search instead of taking all that time ;-) (and I said the same thing about a year ago on this very subject). You'd have found at least one post containing one liner solutions from Hypetia and myself that will tell you whether any particular date is the 1st, 2nd, 3rd 4th or even 5th Monday, Tuesday etc of any given month in any given yera ... Mine had a minor bug in it that I never published a correction for and was less elegant than Hypetia's. So I quote Hypetia's solution below (although with the parameter order altered slightly so it better matches the way we might ask the question:

[blue]Function DateTest(dt As Date, WeekDayofMonth As Integer, DayofWeek As VbDayOfWeek) As Boolean
    DateTest = Weekday(dt) = DayofWeek And (Day(dt) + 6) \ 7 = WeekDayofMonth
End Function[/blue]

So, if we want to know if the 1st May 2009 is the first Friday of the month, we might try:

[blue]MsgBox DateTest("1 May 2009", 1, vbFriday)[/blue]

Or if 31st March 2007 was the 5th Saturday of that month ...:

[blue]MsgBox DateTest("31 March 2009", 5, vbSaturday)[/blue]

And for the actual issue at hand:

[blue]If DateTest(Now, 1, vbMonday) Then
    [green]'do your stuff[/green]
End If[/blue]

 

[HarleyQuinn]

I really did do a keyword search (honest guv, I knew there was code on here somewhere that did this) but the time it was taking to find the solution the OP was after was working out to be much longer than it took to write the function

Plus it was much more interesting than Adobe Illustrator stuff I'm working on at the minute...

Thanks for posting (and editing) Hypetia's solution though, it's much cleaner than mine ever hoped to be

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[VisualBasicHelp]

Thanks guys, for providing a better way of doing the code.
Really appreciated.