Hi,
This assumes the date is in Column C and that there is a header. Start on
the first date, which would be C2, if there is a header. The miles are
assumed to be in Column D. It also assumes you want to total Monday through
Sunday.
Sub Macro1()
StartDateWeekday = Weekday(ActiveCell.Value)
StartAddress = ActiveCell.Address
StartRow = ActiveCell.Row
Do Until ActiveCell.Value = ""
If StartDateWeekday = 1 Then
ActiveCell.Rows("2:2").EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-" & (ActiveCell.Row - StartRow) &
"]C:R[-1]C)"
ActiveCell.Offset(1, -1).Select
StartRow = ActiveCell.Row
StartDateWeekday = Weekday(ActiveCell.Value)
Else
ActiveCell.Offset(1, 0).Select
StartDateWeekday = Weekday(ActiveCell.Value)
End If
Loop
End Sub

Hope this helps.
Thanks,



"HotRod" wrote:

I'm wondering if it is possible to do the following with a formula or if I
need to create a macro. I'd prefer to stay away from the macro if possible.


I Have a sheet that has all of my business trips on it (Sorted by Date) I
want to sum the sheet so that I get the total mileage for each week.

1) Since some weeks I travel more than others how can I determine if the
mileage belongs to week 1 or 2, for ex. One week I may have five business
trips C5:C10 and then week two, two business trips C11:C12, but the
following month it could be reversed. So How can I get another cell to SUM
the first week?


2) Is there a way to sort this out in a formula?

This is what I think I need to figure out

a) Set start date
b) Determine date ranges for week's 1 - 4
c)