Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to add amount to a cell based on category and month | New Users to Excel | |||
Return dates based on month and day of week | Excel Worksheet Functions | |||
Find category value based on date range? | Excel Worksheet Functions | |||
SUM a range of sales based on month | Excel Worksheet Functions | |||
How do I generate a new sheet based on date/week/month? | Excel Worksheet Functions |