Work book with working days only

[Thyag]

Hi All,

I Need the code that can generate sheets with date of all the working
days in a month.

Note - Working days are the days between monday and friday.

Thanks
Thyag

 

[Guest]

here's one way. It will put all week days of the current month in column A.

Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = DateAdd("d", -Day(Date) + 1, Date)
dtEnd = DateAdd("m", 1, dtStart) - 1

dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveSheet.Range("A" & l).Value = dt
l = l + 1
End If
dt = dt + 1
Wend

End Sub

 

[Thyag]

here's one way. It will put all week days of the current month in column A.

Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = DateAdd("d", -Day(Date) + 1, Date)
dtEnd = DateAdd("m", 1, dtStart) - 1

dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveSheet.Range("A" & l).Value = dt
l = l + 1
End If
dt = dt + 1
Wend

End Sub

--
Hope that helps.

Vergel Adriano








- Show quoted text -

Hi Vergel Adriano,

Well, I really appriciate this.

But I need the same dates to appear as the sheet tab names.
Is this possible....?


Thanks,
Thyag

 

[Jana]

Hi Vergel Adriano,

Well, I really appriciate this.

But I need the same dates to appear as the sheet tab names.
Is this possible....?

Thanks,
Thyag- Hide quoted text -

- Show quoted text -

Thyag:

Try this:
Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long


dtStart = DateAdd("d", -Day(Date) + 1, Date)
dtEnd = DateAdd("m", 1, dtStart) - 1


dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveWorkbook.Sheets.Add
After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(dt, "mm-dd-yyyy")
l = l + 1
End If
dt = dt + 1
Wend


End Sub

You can change the format of the sheet names as needed.

HTH,
Jana

 

[Jana]

Thyag:

Try this:
Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = DateAdd("d", -Day(Date) + 1, Date)
dtEnd = DateAdd("m", 1, dtStart) - 1

dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveWorkbook.Sheets.Add
After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(dt, "mm-dd-yyyy")
l = l + 1
End If
dt = dt + 1
Wend

End Sub

You can change the format of the sheet names as needed.

HTH,
Jana- Hide quoted text -

- Show quoted text -

Please note that the line beginning with After:= wrapped to the next
line in my post.

Jana

 

[Thyag]

Please note that the line beginning with After:= wrapped to the next
line in my post.

Jana- Hide quoted text -

- Show quoted text -

This is really wonder ful.

I just want a small modification for this.
Can I generate the same sheets for any specified month.

Thanks,
Thyag.

 

[Jana]

This is really wonder ful.

I just want a small modification for this.
Can I generate the same sheets for any specified month.

Thanks,
Thyag.- Hide quoted text -

- Show quoted text -

Thyag:
This will prompt you for a beginning date, but will error out if you
input anything other than a date into the input box.

Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = CDate(InputBox("Enter desired start date in mm/dd/yyyy
format:", "Begin Date?"))
dtStart = DateAdd("d", -Day(dtStart) + 1, dtStart)
dtEnd = DateAdd("m", 1, dtStart) - 1


dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(dt, "mm-dd-yyyy")
l = l + 1
End If
dt = dt + 1
Wend
End Sub

Again, watch out for any line wrapping.

HTH,
Jana

 

[Thyag]

Thyag:
This will prompt you for a beginning date, but will error out if you
input anything other than a date into the input box.

Sub test()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim l As Long

dtStart = CDate(InputBox("Enter desired start date in mm/dd/yyyy
format:", "Begin Date?"))
dtStart = DateAdd("d", -Day(dtStart) + 1, dtStart)
dtEnd = DateAdd("m", 1, dtStart) - 1

dt = dtStart
l = 1
While dt <= dtEnd
If Application.WorksheetFunction.Weekday(dt, 2) < 6 Then
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(dt, "mm-dd-yyyy")
l = l + 1
End If
dt = dt + 1
Wend
End Sub

Again, watch out for any line wrapping.

HTH,
Jana- Hide quoted text -

- Show quoted text -

Hi,
It is really fantastic.
Is it possible to give only month + year and get the same result...?

Thanks,
Thyag

 

[Guest]

lookup the format function in help and figure it out!

 

[Thyag]

lookup the format function in help and figure it out!

--
Email address is not valid.
Please reply to newsgroup only.







- Show quoted text -

Thanks

 

[Jana]

lookup the format function in help and figure it out!

--
Email address is not valid.
Please reply to newsgroup only.







- Show quoted text -

Thyang:

Although it is possible, sometimes it is easier to deal with a minor
inconvenience (in this case typing 7/1/08 instead of 07/2008) than to
try to program the ideal solution. This solution gives you the
ultimate flexibility for your begin date (what if you want to begin in
the middle of the month for some reason?), while keeping the
likelihood of an invalid entry in the InputBox to a minimum. Also,
for the current year's months, you can just enter July as 7/1 and it
will assume the 2007.

HTH,
Jana