I have a spreadsheet with order date data:
I need to find the average interval in days between each order date. I have to both find a way to get past the blank cells in the row, and also take into account that some clients have 5-10 orders and some clients have 2 orders when calculating my average frequency (interval) between orders.
Sub DateInt()
Dim CurrentSheet As Worksheet
Dim LastRow As Integer
Dim LastCol As Integer
Dim CurrentRow As Integer
Dim CurrentCol As Integer
Dim GrandT As String
Dim DateA As Date
Dim DateB As Date
Dim DateTtl As Integer
Dim DateCount As Integer
Set CurrentSheet = ActiveWorkbook.ActiveSheet
LastRow = CurrentSheet.Range("A" & Rows.Count).End(xlUp).Row - 1
LastCol = CurrentSheet.Cells(4, Columns.Count).End(xlToLeft).Column
Cells(4, LastCol + 1).Value = "Avg Interval"
Cells(4, LastCol + 2).Value = "Days Since Last Order"
Cells(4, LastCol + 3).Value = "Last Order Date"
Cells(4, LastCol + 4).Value = "Last Order v Avg Order"
For CurrentRow = 5 To LastRow
Cells(CurrentRow, LastCol).Value = Date
Cells(CurrentRow, LastCol).NumberFormat = "mm/dd/yy"
DateCount = 0
DateTtl = 0
DateC = DateAdd("d", 20, Date)
For CurrentCol = 2 To LastCol
If Cells(CurrentRow, CurrentCol).Value = "" Then
Else
If DateCount < 1 Then
DateA = Cells(CurrentRow, CurrentCol).Value
Else
DateB = Cells(CurrentRow, CurrentCol).Value
DateTtl = DateDiff("d", DateA, DateB) + DateTtl
If DateValue(DateB) = DateValue(Date) Then
Else
DateA = DateB
End If
End If
DateCount = DateCount + 1
End If
Next CurrentCol
Cells(CurrentRow, LastCol + 1).Value = DateTtl / DateCount
Cells(CurrentRow, LastCol + 1).NumberFormat = "General"
Cells(CurrentRow, LastCol + 2).Value = DateDiff("d", DateA, Date)
Cells(CurrentRow, LastCol + 2).NumberFormat = "General"
Cells(CurrentRow, LastCol + 3).Value = DateA
Cells(CurrentRow, LastCol + 3).NumberFormat = "mm/dd/yy"
Cells(CurrentRow, LastCol + 4).Value = Cells(CurrentRow, LastCol + 1).Value - Cells(CurrentRow, LastCol + 2).Value
Cells(CurrentRow, LastCol + 4).NumberFormat = "#,##0_);[Red](#,##0)"
Next CurrentRow
End Sub
Here is what my code ended up doing: