Hi,

Suppose in Sheet1 i have mentioned few days of holidays which company
provided us in the entre year:

S.No Occasion Date
1 a 26th Jan 2007
2 b 15th Aug 2007

and so on....

Now i want to macro which can give me the output in the Sheet2

counting all the Sundays & Saturday along with their Date including the
Dates mentioned in the Sheet1. & the the Date should get filtered in
the Assending order. 1st Jan then 2nd Feb, 3 March

Is this possible

Awaiting for your reply

Thanks

Akash

Hi

Without a macro.
On sheet2 enter in cell A1
06/01/2007
In A2 enter =A1+1
in A3 enter =A1+7
Mark cells A2:A3 and copy down to A106

Add your list of holiday dates from Sheet1 at cell A107 onward.
Sort column A Ascending.

--
Regards

Roger Govier


"Akash" wrote in message
ups.com...
Hi,

Suppose in Sheet1 i have mentioned few days of holidays which company
provided us in the entre year:

S.No Occasion Date
1 a 26th Jan 2007
2 b 15th Aug 2007

and so on....

Now i want to macro which can give me the output in the Sheet2

counting all the Sundays & Saturday along with their Date including
the
Dates mentioned in the Sheet1. & the the Date should get filtered in
the Assending order. 1st Jan then 2nd Feb, 3 March

Is this possible

Awaiting for your reply

Thanks

Akash

Dear Martin,

what i want in Sheet 2 the output would be:
Sr No Date Occasion
1 01 January 2006 Sunday
2 07 January 2006 Saturday
3 08 January 2006 Sunday
4 14 January 2006 Saturday
5 15 January 2006 Sunday
6 21 January 2006 Saturday
7 22 January 2006 Sunday
8 26 January 2006 a
9 28 January 2006 Saturday
10 29 January 2006 Sunday

I hope i have given the reasonable example to make u understand whats
my requirement.

Just to


On Dec 13, 1:35 pm, Martin Fishlock
wrote:
Akash,

Please can you explain a little mmore with some examples of sheet two.

I can't work out what you want.

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

"Akash" wrote:
Hi,

Suppose in Sheet1 i have mentioned few days of holidays which company
provided us in the entre year:

S.No Occasion Date
1 a 26th Jan 2007
2 b 15th Aug 2007

and so on....

Now i want to macro which can give me the output in the Sheet2

counting all the Sundays & Saturday along with their Date including the
Dates mentioned in the Sheet1. & the the Date should get filtered in
the Assending order. 1st Jan then 2nd Feb, 3 March

Is this possible

Awaiting for your reply

Thanks

Akash

Akash

This should work based on the sheet one in the format from row 2
a=nr
b=date
c=name

Sub nonworkingdays()
Dim wssp As Worksheet, wsnwd As Worksheet
Dim yy As Integer
Dim fnwd As Integer
Dim lRowSh1 As Long, lRowSh2 As Long
Dim d As Date, ldy As Date
Dim bSpDay As Boolean

Set wssp = ActiveWorkbook.Worksheets("Sheet1")
Set wsnwd = ActiveWorkbook.Worksheets("Sheet2")

lRowSh1 = 2
lRowSh2 = 2
'headings
wsnwd.Cells(1, 1) = "Sr No"
wsnwd.Cells(1, 2) = "Date"
wsnwd.Cells(1, 3) = "Occasion"

' get year from holdiay table other wise set
yy = Year(wssp.Range("C2"))
d = DateSerial(yy, 1, 1) ' 1'Jan
ldy = DateSerial(yy, 12, 31) '31'Dec
fnwd = Weekday(d, vbMonday) ' first day of yr 1=mon
If fnwd < 6 Then d = d + 6 - fnwd ' find first sat/sun
Do While d <= ldy 'loop thru yr
bSpDay = wssp.Cells(lRowSh1, 1) < ""
' check for sp days
' end on blank row and assume in order
If bSpDay Then
bSpDay = wssp.Cells(lRowSh1, 3) < d 'less than current day
End If
If bSpDay Then ' if special day deal with it
wsnwd.Cells(lRowSh2, 1) = lRowSh2 - 1
wsnwd.Cells(lRowSh2, 2) = wssp.Cells(lRowSh1, 3)
wsnwd.Cells(lRowSh2, 2).NumberFormat = "dd Mmmm yyyy"
wsnwd.Cells(lRowSh2, 3) = wssp.Cells(lRowSh1, 2)
lRowSh1 = lRowSh1 + 1
Else ' just a sat/sun
wsnwd.Cells(lRowSh2, 1) = lRowSh2 - 1
wsnwd.Cells(lRowSh2, 2) = d
wsnwd.Cells(lRowSh2, 2).NumberFormat = "dd Mmmm yyyy"
wsnwd.Cells(lRowSh2, 3) = Format(d, "Dddd")
If Weekday(d, vbSaturday) = 1 Then ' next day
d = d + 1
Else
d = d + 6
End If
End If
lRowSh2 = lRowSh2 + 1 ' next row
Loop

End Sub

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


"Akash" wrote:

Dear Martin,

what i want in Sheet 2 the output would be:
Sr No Date Occasion
1 01 January 2006 Sunday
2 07 January 2006 Saturday
3 08 January 2006 Sunday
4 14 January 2006 Saturday
5 15 January 2006 Sunday
6 21 January 2006 Saturday
7 22 January 2006 Sunday
8 26 January 2006 a
9 28 January 2006 Saturday
10 29 January 2006 Sunday

I hope i have given the reasonable example to make u understand whats
my requirement.

Just to


On Dec 13, 1:35 pm, Martin Fishlock
wrote:
Akash,

Please can you explain a little mmore with some examples of sheet two.

I can't work out what you want.

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

"Akash" wrote:
Hi,

Suppose in Sheet1 i have mentioned few days of holidays which company
provided us in the entre year:

S.No Occasion Date
1 a 26th Jan 2007
2 b 15th Aug 2007

and so on....

Now i want to macro which can give me the output in the Sheet2

counting all the Sundays & Saturday along with their Date including the
Dates mentioned in the Sheet1. & the the Date should get filtered in
the Assending order. 1st Jan then 2nd Feb, 3 March

Is this possible

Awaiting for your reply

Thanks

Akash

Amazing Work,,,


Thanks a ton

More over if we want only to track Sunday then what shoud we omit,

Awaiting for ur mail....

Regards

Akash
Martin Fishlock wrote:
Akash

This should work based on the sheet one in the format from row 2
a=nr
b=date
c=name

Sub nonworkingdays()
Dim wssp As Worksheet, wsnwd As Worksheet
Dim yy As Integer
Dim fnwd As Integer
Dim lRowSh1 As Long, lRowSh2 As Long
Dim d As Date, ldy As Date
Dim bSpDay As Boolean

Set wssp = ActiveWorkbook.Worksheets("Sheet1")
Set wsnwd = ActiveWorkbook.Worksheets("Sheet2")

lRowSh1 = 2
lRowSh2 = 2
'headings
wsnwd.Cells(1, 1) = "Sr No"
wsnwd.Cells(1, 2) = "Date"
wsnwd.Cells(1, 3) = "Occasion"

' get year from holdiay table other wise set
yy = Year(wssp.Range("C2"))
d = DateSerial(yy, 1, 1) ' 1'Jan
ldy = DateSerial(yy, 12, 31) '31'Dec
fnwd = Weekday(d, vbMonday) ' first day of yr 1=mon
If fnwd < 6 Then d = d + 6 - fnwd ' find first sat/sun
Do While d <= ldy 'loop thru yr
bSpDay = wssp.Cells(lRowSh1, 1) < ""
' check for sp days
' end on blank row and assume in order
If bSpDay Then
bSpDay = wssp.Cells(lRowSh1, 3) < d 'less than current day
End If
If bSpDay Then ' if special day deal with it
wsnwd.Cells(lRowSh2, 1) = lRowSh2 - 1
wsnwd.Cells(lRowSh2, 2) = wssp.Cells(lRowSh1, 3)
wsnwd.Cells(lRowSh2, 2).NumberFormat = "dd Mmmm yyyy"
wsnwd.Cells(lRowSh2, 3) = wssp.Cells(lRowSh1, 2)
lRowSh1 = lRowSh1 + 1
Else ' just a sat/sun
wsnwd.Cells(lRowSh2, 1) = lRowSh2 - 1
wsnwd.Cells(lRowSh2, 2) = d
wsnwd.Cells(lRowSh2, 2).NumberFormat = "dd Mmmm yyyy"
wsnwd.Cells(lRowSh2, 3) = Format(d, "Dddd")
If Weekday(d, vbSaturday) = 1 Then ' next day
d = d + 1
Else
d = d + 6
End If
End If
lRowSh2 = lRowSh2 + 1 ' next row
Loop

End Sub

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


"Akash" wrote:

Dear Martin,

what i want in Sheet 2 the output would be:
Sr No Date Occasion
1 01 January 2006 Sunday
2 07 January 2006 Saturday
3 08 January 2006 Sunday
4 14 January 2006 Saturday
5 15 January 2006 Sunday
6 21 January 2006 Saturday
7 22 January 2006 Sunday
8 26 January 2006 a
9 28 January 2006 Saturday
10 29 January 2006 Sunday

I hope i have given the reasonable example to make u understand whats
my requirement.

Just to


On Dec 13, 1:35 pm, Martin Fishlock
wrote:
Akash,

Please can you explain a little mmore with some examples of sheet two.

I can't work out what you want.

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

"Akash" wrote:
Hi,

Suppose in Sheet1 i have mentioned few days of holidays which company
provided us in the entre year:

S.No Occasion Date
1 a 26th Jan 2007
2 b 15th Aug 2007

and so on....

Now i want to macro which can give me the output in the Sheet2

counting all the Sundays & Saturday along with their Date including the
Dates mentioned in the Sheet1. & the the Date should get filtered in
the Assending order. 1st Jan then 2nd Feb, 3 March

Is this possible

Awaiting for your reply

Thanks

Akash