Print Week Category based on range for month
 

Hi,

I am Ajith from Bangalore.
I have a date field in my excel sheet with field name week in Cloumn K
(11th Column). Format is date dd-mmm-yy (eg 3-Oct-08 )
with different dates.

I have a to categorise these date in column K(11) based on week.
If the date falls in week 1 of any month i.e from 1-7, then it should be
printed in the same row in L (12 th ) cloumn named as Week1.
similary if it falls in the 8th to 14 th of any month then Week2 should be
printed in Lth column of the same row.

Similarly for week3, week4 and week 5 in multiples of 7.

I have written a macro for this .Below is the code for this.
I am not able to crack the result even though the macro is not giving any
error.
Please help.

________________________________________________

Sub DateRangeforWeek()

Dim r As Long
Dim DayOfWeek As Long

For r = Cells(Rows.Count, 11).End(xlUp).Row To 2 Step -1

DayOfWeek = Day(Cells(r, 11))

'DayOfWeek =
WeekdayName(Weekday(ThisWorkbook.Sheets("HSOTLTime DetailsWS").Range("Date").Value), 0, 1)

If DayOfWeek 0 And DayOfWeek < 8 Then
Cells(r, 12) = "Week1"
End If
If DayOfWeek 7 And DayOfWeek < 15 Then
Cells(r, 12) = "Week2"
End If
If DayOfWeek 14 And DayOfWeek < 22 Then
Cells(r, 12) = "Week3"
End If
If DayOfWeek 21 And DayOfWeek < 29 Then
Cells(r, 12) = "Week4"
End If
If DayOfWeek 28 Then
Cells(r, 12) = "Week5"
End If
Next r

End Sub

Regards,
Ajith.

Print Week Category based on range for month
 

Hi,

Please Note that for my below query Column name for "Kth" i.e 11th Cloumn
is "Date" and Column Name for "Lth" i.e 12th Column is "Week1".

"Ajith" wrote:

Hi,

I am Ajith from Bangalore.
I have a date field in my excel sheet with field name week in Cloumn K
(11th Column). Format is date dd-mmm-yy (eg 3-Oct-08 )
with different dates.

I have a to categorise these date in column K(11) based on week.
If the date falls in week 1 of any month i.e from 1-7, then it should be
printed in the same row in L (12 th ) cloumn named as Week1.
similary if it falls in the 8th to 14 th of any month then Week2 should be
printed in Lth column of the same row.

Similarly for week3, week4 and week 5 in multiples of 7.

I have written a macro for this .Below is the code for this.
I am not able to crack the result even though the macro is not giving any
error.
Please help.

________________________________________________

Sub DateRangeforWeek()

Dim r As Long
Dim DayOfWeek As Long

For r = Cells(Rows.Count, 11).End(xlUp).Row To 2 Step -1

DayOfWeek = Day(Cells(r, 11))

'DayOfWeek =
WeekdayName(Weekday(ThisWorkbook.Sheets("HSOTLTime DetailsWS").Range("Date").Value), 0, 1)

If DayOfWeek 0 And DayOfWeek < 8 Then
Cells(r, 12) = "Week1"
End If
If DayOfWeek 7 And DayOfWeek < 15 Then
Cells(r, 12) = "Week2"
End If
If DayOfWeek 14 And DayOfWeek < 22 Then
Cells(r, 12) = "Week3"
End If
If DayOfWeek 21 And DayOfWeek < 29 Then
Cells(r, 12) = "Week4"
End If
If DayOfWeek 28 Then
Cells(r, 12) = "Week5"
End If
Next r

End Sub

Regards,
Ajith.

Any body to crack it ?: Print Week Category based on range for mon
 

"Ajith" wrote:

Hi,

Please Note that for my below query Column name for "Kth" i.e 11th Cloumn
is "Date" and Column Name for "Lth" i.e 12th Column is "Week1".

"Ajith" wrote:

Hi,

I am Ajith from Bangalore.
I have a date field in my excel sheet with field name week in Cloumn K
(11th Column). Format is date dd-mmm-yy (eg 3-Oct-08 )
with different dates.

I have a to categorise these date in column K(11) based on week.
If the date falls in week 1 of any month i.e from 1-7, then it should be
printed in the same row in L (12 th ) cloumn named as Week1.
similary if it falls in the 8th to 14 th of any month then Week2 should be
printed in Lth column of the same row.

Similarly for week3, week4 and week 5 in multiples of 7.

I have written a macro for this .Below is the code for this.
I am not able to crack the result even though the macro is not giving any
error.
Please help.

________________________________________________

Sub DateRangeforWeek()

Dim r As Long
Dim DayOfWeek As Long

For r = Cells(Rows.Count, 11).End(xlUp).Row To 2 Step -1

DayOfWeek = Day(Cells(r, 11))

'DayOfWeek =
WeekdayName(Weekday(ThisWorkbook.Sheets("HSOTLTime DetailsWS").Range("Date").Value), 0, 1)

If DayOfWeek 0 And DayOfWeek < 8 Then
Cells(r, 12) = "Week1"
End If
If DayOfWeek 7 And DayOfWeek < 15 Then
Cells(r, 12) = "Week2"
End If
If DayOfWeek 14 And DayOfWeek < 22 Then
Cells(r, 12) = "Week3"
End If
If DayOfWeek 21 And DayOfWeek < 29 Then
Cells(r, 12) = "Week4"
End If
If DayOfWeek 28 Then
Cells(r, 12) = "Week5"
End If
Next r

End Sub

Regards,
Ajith.

Print Week Category based on range for month
 

So what's the problem? While I'm the parent of a teenager, I still haven't
learned to be a mindreader.

FWIW, I think I'd want to see FISCAL WEEKS, which don't always start on the
first of the month.

Have you considered using the WEEKNUM formula?

HTH,
Barb Reinhardt

If this post answers your question, click YES below.


"Ajith" wrote:

Hi,

I am Ajith from Bangalore.
I have a date field in my excel sheet with field name week in Cloumn K
(11th Column). Format is date dd-mmm-yy (eg 3-Oct-08 )
with different dates.

I have a to categorise these date in column K(11) based on week.
If the date falls in week 1 of any month i.e from 1-7, then it should be
printed in the same row in L (12 th ) cloumn named as Week1.
similary if it falls in the 8th to 14 th of any month then Week2 should be
printed in Lth column of the same row.

Similarly for week3, week4 and week 5 in multiples of 7.

I have written a macro for this .Below is the code for this.
I am not able to crack the result even though the macro is not giving any
error.
Please help.

________________________________________________

Sub DateRangeforWeek()

Dim r As Long
Dim DayOfWeek As Long

For r = Cells(Rows.Count, 11).End(xlUp).Row To 2 Step -1

DayOfWeek = Day(Cells(r, 11))

'DayOfWeek =
WeekdayName(Weekday(ThisWorkbook.Sheets("HSOTLTime DetailsWS").Range("Date").Value), 0, 1)

If DayOfWeek 0 And DayOfWeek < 8 Then
Cells(r, 12) = "Week1"
End If
If DayOfWeek 7 And DayOfWeek < 15 Then
Cells(r, 12) = "Week2"
End If
If DayOfWeek 14 And DayOfWeek < 22 Then
Cells(r, 12) = "Week3"
End If
If DayOfWeek 21 And DayOfWeek < 29 Then
Cells(r, 12) = "Week4"
End If
If DayOfWeek 28 Then
Cells(r, 12) = "Week5"
End If
Next r

End Sub

Regards,
Ajith.

Print Week Category based on range for month
 

Dear Barb,

I need the Monthly timesheet details this should be split in to week wise
say from Monday to Sunday for any month any year (can be future date as well
as past date).

Please Help me in correcting the code.

"Barb Reinhardt" wrote:

So what's the problem? While I'm the parent of a teenager, I still haven't
learned to be a mindreader.

FWIW, I think I'd want to see FISCAL WEEKS, which don't always start on the
first of the month.

Have you considered using the WEEKNUM formula?

HTH,
Barb Reinhardt

If this post answers your question, click YES below.


"Ajith" wrote:

Hi,

I am Ajith from Bangalore.
I have a date field in my excel sheet with field name week in Cloumn K
(11th Column). Format is date dd-mmm-yy (eg 3-Oct-08 )
with different dates.

I have a to categorise these date in column K(11) based on week.
If the date falls in week 1 of any month i.e from 1-7, then it should be
printed in the same row in L (12 th ) cloumn named as Week1.
similary if it falls in the 8th to 14 th of any month then Week2 should be
printed in Lth column of the same row.

Similarly for week3, week4 and week 5 in multiples of 7.

I have written a macro for this .Below is the code for this.
I am not able to crack the result even though the macro is not giving any
error.
Please help.

________________________________________________

Sub DateRangeforWeek()

Dim r As Long
Dim DayOfWeek As Long

For r = Cells(Rows.Count, 11).End(xlUp).Row To 2 Step -1

DayOfWeek = Day(Cells(r, 11))

'DayOfWeek =
WeekdayName(Weekday(ThisWorkbook.Sheets("HSOTLTime DetailsWS").Range("Date").Value), 0, 1)

If DayOfWeek 0 And DayOfWeek < 8 Then
Cells(r, 12) = "Week1"
End If
If DayOfWeek 7 And DayOfWeek < 15 Then
Cells(r, 12) = "Week2"
End If
If DayOfWeek 14 And DayOfWeek < 22 Then
Cells(r, 12) = "Week3"
End If
If DayOfWeek 21 And DayOfWeek < 29 Then
Cells(r, 12) = "Week4"
End If
If DayOfWeek 28 Then
Cells(r, 12) = "Week5"
End If
Next r

End Sub

Regards,
Ajith.