subtotal by week with duplicate dates?

[Beth]

I'm trying to subtotal data by calendar week with duplicate and
non-consecutive dates - please help? Column A represents an invoice date,
Column B represents the qty shipped. I need the total qty shipped per week?

Date QTY
1/4/06 864
1/4/06 192
1/5/06 144
1/5/06 192
1/5/06 48
1/5/06 528
1/6/06 48
1/9/06 384
1/9/06 240
1/10/06 1,008.00
1/10/06 480
1/10/06 768
1/10/06 720
1/10/06 240

Thanks!

 

[Joel]

The macro below will work. It is not 100% bullet proof. It tests if a new
week occurs by check the day of the week. If the day of the week of the
nextt line is less than the day of the week of the present line it performs a
subtotal

for eample
row 10 is Friday
row 11 is Monday

the code says friday is the end of the week. this doesn't work in every
case, it only works if you have data every day. I can make the macro better
if you need it changed. I took the simple approach first. there is a
function WEEKNUM but it doesn't work on my PC.



StartRow = 2 'start after header row
RowCount = StartRow
OldDayofWeek = Weekday(Range("A2"))
FirstRow = StartRow
Do While Range("A" & RowCount) <> ""
If IsDate(Range("A" & (RowCount + 1))) Then
NewDayofWeek = Weekday(Range("A" & (RowCount + 1)))
Else
NewDayofWeek = 0
End If
If NewDayofWeek < OldDayofWeek Then
Range("C" & RowCount).Formula = _
"=Sum(B" & FirstRow & ":B" & RowCount & ")"
OldDayofWeek = NewDayofWeek
FirstRow = RowCount + 1
End If
RowCount = RowCount + 1
Loop

 

[Joel]

I figured a very easy way to determine when a new week starts.

I chose a Monday earlier than any of the dates in your worksheet as a
referrence point which is Monday Jan 3, 2000.

Excel dates start at Jan 1, 1900 which equals 1. Every Day equal one. An
hour equals 1/24 and a minute equals 1/1440 (1440 = 24 * 60).

MY solution really sets Jan 3 2000 as 0
Jan 3 2000 = 0
Jan 4 2000 = 1
Jan 5 2000 = 2
Jan 6 2000 = 3
Jan 7 2000 = 4
Jan 8 2000 = 5
Jan 9 2000 = 6
Jan 10 2000 = 7
Jan 11 2000 = 8
Jan 12 2000 = 9
Jan 13 2000 = 10
Jan 14 2000 = 11
Jan 15 2000 = 12
Jan 16 2000 = 13
Jan 17 2000 = 14

If you take any date and subtract it from Jan 3 2000 you ghet the following
week 1 - Jan 3 to Jan 9 = 0 to 6.99999, then divide by 7 = 0 to .999999.
round down to the next integer always will get 0
week 2 - Jan 10 to Jan 16 = 7 to 13.99999, then divide by 7 = 0 to .999999
round down to the next integer always will get 1

Using this algorithm for any two dates will alweays get the same number for
any day in one week and will get a differrent number in a different week

This is the code I used
OldWeekNumber = Int((Range("A2") - FirstMonday) / 7)


Sub get_subtotals()

'count weeks from this date
FirstMonday = DateValue("1/3/2000")


StartRow = 2 'start after header row
RowCount = StartRow
OldWeekNumber = Int((Range("A2") - FirstMonday) / 7)
FirstRow = StartRow
Do While Range("A" & RowCount) <> ""
If IsDate(Range("A" & (RowCount + 1))) Then
NewWeekNumber = Int((Range("A" & (RowCount + 1)) - _
FirstMonday) / 7)
Else
'used at end of data to force a subtotal
NewWeekNumber = 0
End If
If NewWeekNumber <> OldWeekNumber Then
Range("C" & RowCount).Formula = _
"=Sum(B" & FirstRow & ":B" & RowCount & ")"
OldWeekNumber = NewWeekNumber
FirstRow = RowCount + 1
End If
RowCount = RowCount + 1
Loop

End Sub