Calculate days between subtotaled list

[hutch@edge.net]

Good day, Gurus.

Not sure if this is best accomplished via VBA or Formula, but I know all the smartest people hang out in the Programming forum, so here goes.

In the attached workbook, I have subtotaled a list of parts. I want to add a formula beside the subtotals, under Column C, "Days BO", that subtracts earliest date in column R from the latest date in column R, for each part number.

I have added a simple formula to the attached example that does what I want, but the full sheet will be thousands of lines weekly, with varying part numbers, and different counts. I don't know how to take the count function into account so I can copy a single formula all the way down the sheet.

Thanks in advance for any help you can offer.

[JBeaucaire]

Where on your sheet are you talking about? I don't see any date functions other than the dates themselves in column R.

Can you provide several answers for some of the rows on your example sheet and highlight them so we can see what your goal is?

[hutch@edge.net]

You are correct, JB. The dates I want to calculate are located in column R. I want to count how many days passed between the first date the part was backordered and the last date,

I have attached a new zip file, showing several examples in green. On any parts with only one entry, the formula should always return a 1. If there are multiple entries, it should count the number of days between the earliest and the latest entry.

Thanks.

[JBeaucaire]

Ah, I see it now in column C. This should take care of it, run this macro to update your column C values:

Option Explicit

Sub AddDaysBO()
Dim lastrow As Long, firstrow As Long, i As Integer
Application.ScreenUpdating = False
lastrow = Range("A" & Rows.Count).End(xlUp).Row - 1   'takes Grand Total off end
firstrow = 2

For i = 2 To lastrow
    If Range("A" & i).Value Like "*Total" Then
        Range("C" & i).FormulaR1C1 = "=R" & i - 1 & "C18-R" & firstrow & "C18"
        Range("C" & i).NumberFormat = "General"
        firstrow = i + 1
    End If
Next i

Application.ScreenUpdating = True
End Sub

[hutch@edge.net]

Works beautifully, JB.

Thanks!!