Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,355
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to add amount to a cell based on category and month gabrielinlompoc New Users to Excel 7 April 22nd 08 01:15 PM
Return dates based on month and day of week lesg46 Excel Worksheet Functions 8 January 4th 07 01:47 AM
Find category value based on date range? thewiz1972 Excel Worksheet Functions 5 June 10th 06 03:28 AM
SUM a range of sales based on month Manos Excel Worksheet Functions 9 May 8th 06 10:30 AM
How do I generate a new sheet based on date/week/month? Jay Excel Worksheet Functions 1 January 10th 06 09:50 AM


All times are GMT +1. The time now is 04:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"