HOW TO TRACK DATES OF A MONTH EXCEPT SUNDAY
 

Hi,

Is this possible to track dates of a month except Sunday by running a
macro.

Akash

HOW TO TRACK DATES OF A MONTH EXCEPT SUNDAY
 

Dear Sir,

I want that as soon as the User presses a shortcut key in Excel then
the system should prompt for the Month & the year and as soon as the
user enters January 2007 then the system should generate track dates of
a month except Sunday.

Is it possible.

Pls do help me in this regards

Akash Maheshwari


On Dec 30, 1:29 pm, "Akash" wrote:
Hi,

Is this possible to track dates of a month except Sunday by running a
macro.

Akash

HOW TO TRACK DATES OF A MONTH EXCEPT SUNDAY
 

Yes it is try the follwoing code:

[B1]=DATE(2007,1,1)+IF(WEEKDAY(DATE(2007,1,1))=7,1,0)
[B2]=B1+IF(WEEKDAY(B1,1)=7,2,1)

and then copy b2 down for the year.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Akash" wrote:

Hi,

Is this possible to track dates of a month except Sunday by running a
macro.

Akash

HOW TO TRACK DATES OF A MONTH EXCEPT SUNDAY
 

Hi Martin,

Thanks for the wonderful solution. Its working very fine.

Moreover if this can be done through Macro it would be very benificial
for us.

I want that as soon as the User presses a shortcut key in Excel then
the system should prompt for the Month & the year and as soon as the
user enters January 2007 then the system should generate track dates of
a month except Sunday.

Is it possible.

Pls do help me in this regards

Akash Maheshwari



On Dec 30, 2:09 pm, Martin Fishlock
wrote:
Yes it is try the follwoing code:

[B1]=DATE(2007,1,1)+IF(WEEKDAY(DATE(2007,1,1))=7,1,0)
[B2]=B1+IF(WEEKDAY(B1,1)=7,2,1)

and then copy b2 down for the year.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

"Akash" wrote:
Hi,

Is this possible to track dates of a month except Sunday by running a
macro.

Akash

HOW TO TRACK DATES OF A MONTH EXCEPT SUNDAY
 

This should work as a starter, you may need to modify the input and you could
use a userform if you so desired.:

Option Explicit

Sub filldate()

Dim s As String
Dim d As Date
Dim m As Integer
Dim y As Integer
Dim r As Long ' row offset

On Error Resume Next

r = 0
Do
s = InputBox("Enter the year", _
"Working days ...", Year(Now()))
If s = "" Then Exit Sub
y = s
Loop Until y 2000 And y < Year(Now()) + 10
Do
s = InputBox("Enter the month [1.12]", _
"Working days ...", Month(Now()))
If s = "" Then Exit Sub
m = s
Loop Until m 0 And m < 13

d = DateSerial(y, m, 1)

With ActiveSheet.Range("A2")
Do While Month(d) = m
If Weekday(d, vbSunday) < vbSunday Then
.Offset(r, 0) = d
.Offset(r, 0).NumberFormat = _
"dddd, dd.mm.yyyy"
r = r + 1
End If
d = d + 1
Loop
End With

End Sub

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Akash" wrote:

Hi Martin,

Thanks for the wonderful solution. Its working very fine.

Moreover if this can be done through Macro it would be very benificial
for us.

I want that as soon as the User presses a shortcut key in Excel then
the system should prompt for the Month & the year and as soon as the
user enters January 2007 then the system should generate track dates of
a month except Sunday.

Is it possible.

Pls do help me in this regards

Akash Maheshwari



On Dec 30, 2:09 pm, Martin Fishlock
wrote:
Yes it is try the follwoing code:

[B1]=DATE(2007,1,1)+IF(WEEKDAY(DATE(2007,1,1))=7,1,0)
[B2]=B1+IF(WEEKDAY(B1,1)=7,2,1)

and then copy b2 down for the year.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

"Akash" wrote:
Hi,

Is this possible to track dates of a month except Sunday by running a
macro.

Akash