First and last Thursday of every month 1

[stalo]

I am preparing an appointmemt scheduler for a hospital and there are two clinics that want to have appointments, the one every first Thursday of each month and the other every last Thursday of each month.

I have checked the forum but could not find exactly what I was looking for.

Any suggestion will be much appreciated.

 

[KenReay]

Hi

See Day() function in help, that should enable you to enforce appointments on Thursday (or any other day)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jimlee]

something like this for the first Thursday?

if weekday(Date) = vbThursday
dim strDate as string
strDate = left$(Date,2)
select case strDate
case "01", "02", "03", "04", "05", "06", "07"
'set appointment here for 1st Thursday of month
end select
end if




jimlad

 

[stalo]

What I am looking for is an automated way to press a button and go to the first or last Thursday of the month. In the form I press a button and I calculate the next available appointment date with code. I do some checking and if the appointment date is full I go to the next available date. The problem is finding the first and last Thursday of the month.

 

[TonyJollans]

Hi stalo,

The first Thursday of the current month is:

[blue]DateSerial(Year(Date), Month(Date), 8) - Weekday(DateSerial(Year(Date), Month(Date), 8), vbFriday)[/blue]

and The last Thursday is:

[blue]DateSerial(Year(Date), Month(Date) + 1, 1) - Weekday(DateSerial(Year(Date), Month(Date) + 1, 1), vbFriday)[/blue]

If you want a different month, you'll need to substitute the appropriate date, for example ..

[blue]MyDate = #5/1/2004#
DateSerial(Year(MyDate), Month(MyDate), 8) - Weekday(DateSerial(Year(MyDate), Month(MyDate), 8), vbFriday)[/blue]

should give the first Thursday in May 2004

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[jimlee]

vrey handy, thanks

jimlad

 

[stalo]

Dear Tony

Your solution is exactly what I wanted. I have included them in two functions and I rotate through the available Thursdays.

But unfortunately the code for the Last Thursday does not work properly because when is year 2006 it says 2005 and it does not change. The code for First Thursday loops OK though years. What should I change?

 

[stalo]

Dear Tony

Ignore last message. I had a misspelling somewhere, your solution is excellent! Thank you very much!

Rgrds
Stalo