Date Codes in VBA Excel 2000

[apeoortje]

I'm having problem with a Excel VBA marco I'm writting, but I need VBA to tell me when the beginning and the end of every week in the current month.

At the moment I have it going through an input box, but I want to cut out Human error.

Can anyone help me

Thanks
Terry

 

[PHV]

Take a look at the Weekday function

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[apeoortje]

I thought about that first of all, but I'm not sure how to use that funcation.

Thanx

Terry

 

[xlbo]

check it out in the help file then. We are here to help you do your job - not to do your job for you

you also need to be clearer about what you want - do you want the start date and end date of every COMPLETE week in the month or every week that has some element in the month ?? If the latter, do you want the start AND end dates or just the ones that fall into the month

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[apeoortje]

Sorry I'm new at this.

I'm after every week that has some element, but only the beginning and end dates that fall in the month.

Thanks very much

Terry

 

[xlbo]

In that case, you need to get the start and end date of the month (I would suggest using the NOW function for the start of the year - startDate = "01/" & format(now,"mm/yyyy") ) and use them as the start and end of a loop

eg

For i = startDate to endDate step 1

'logic goes here

Next i

In the "logic goes here", you need to test each incrementing date for being either the start or end of a week, using the weekday function - you have not defined what the start and end of a week is for you so I will leave that up to you to figure out

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[docjohn52]

datediff("first date","second date","D")
will rturn the number of days in between dates but they must be entered as strings, within quotes except for cells.
Now() won't work.
datediff("5/1/2004", "6/1/2004","D") gives me 31
or
a$="5/1/2004"
datediff(A$,"6/1/2004","D") gives me 31
or
setting B1 to =5/1/2004
datediff(B1,"6/1/2004","D") gives me 31

Knowing a month's ending date is the key
Hope this helps...

 

[apeoortje]

I've managed to get it to work now!!! thank you very much for your help!!!

Terry