Date set and iteration

Derek at Potters Clay

I want to create a spreadsheet to go to the first day of the current month and set a date if the day is a weekday and not a weekend(or holiday if that is possible). I will also want to check against the sheets in the spreadsheet as I have sheets numbered 1 through 31 and will be hiding each day that is not a regular business day. I basically just need to know how to find the current day in the month and iterate through it as well as set the format for the date in excel.

Peter Mo.

Hi

A few functions ...

Day - day in the month

Month - month in the year

Year - year number

Weekday - day number in week

WeekdayName - name of weekday

Format - returns a variant containing an expression formatted according to instructions

Is this what you were looking for

Regards

PeterMo.

Derek at Potters Clay

Code Snippet

Dim dateit As Date
Dim curdate As date

curdate = Date
dateit = DateSerial(year(curdate), month(curdate), 1)

this is what i'm trying to do but I get an expected array error. after doing this, I will be iterating through the entire month to find out what days are weekdays.

Peter Mo.

Hi

Maybe this sample code will help ...

Dim wsNo As Integer
Dim ThisDate As Date
Dim NextMonth As Date
Dim ThisMonth As Integer
Dim ThisYear As Integer
Dim ThisDay As Integer

ThisMonth = Month(Date)
ThisYear = Year(Date)

NextMonth = DateSerial(ThisYear, ThisMonth + 1, 1)

For wsNo = 1 To 31
ThisDate = DateSerial(ThisYear, ThisMonth, wsNo)
ThisDay = Weekday(ThisDate, vbSunday)
If ThisDate >= NextMonth Or _

ThisDay = vbSaturday Or _

ThisDay = vbSunday Then

Sheets(wsNo).Visible = False
Else
Sheets(wsNo).Visible = True
Sheets(wsNo).Name = Format(wsNo, "00 ") & _

WeekdayName(ThisDay, False, vbSunday)
End If
Next wsNo

MsgBox "Done"

Regards

Peter Mo.

Date set and iteration

Re: Date set and iteration

Re: Date set and iteration

Re: Date set and iteration