Need to know for how long a report runs for every day

Question

I need to run a formula to know how long the report takes to complete I try to get the Min and Max with an array but didn't work I don't know if the date format is the problem array used:

=MIN(IF($B$2:$B$6077=$D4,IF(INT($A$2:$A$6077)=E$1,$A$2:$A$6077)))

https://filebin.net/vzaql7w9qaim31l1

Answer 1

I know the request was for a formula or macro, but here is a procedure that works. The data in Sheet1 has multiple days for each report that may run at different times, and the span of time from 10/1 thru 10/31 may give wrong results.

We need to first get the min&max DateTime of a given Date, and compute the Minutes that the report took. That is then wrapped in an outer Select that looks for the maxMinutes of any date for each given report.

Option Explicit
' needs  Tools/References --> Microsoft ActiveX Data Objects 6.1 Library
Sub doIt()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strFile As String, strCon As String
    Dim strSQL As String

    strFile = ThisWorkbook.FullName
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    cn.Open strCon

    ' first gives 1600 rows which is too much detail
    'strSQL = "SELECT  mid$(DateTime,1,10) as YMD, min(DateTime) as minDT, max(DateTime) as maxDT, DATEDIFF('n', minDT, maxDT) as Minutes, ReportName  FROM [Sheet1$] Group By ReportName, mid$(DateTime,1,10)"

    ' final gives just 48 rows showing biggest time for each report.
    strSQL = "SELECT ReportName, max(Minutes) as maxMinutes From    "
    strSQL = strSQL & " ( SELECT  mid$(DateTime,1,10) as YMD, min(DateTime) as minDT, max(DateTime) as maxDT, DATEDIFF('n', minDT, maxDT) as Minutes, ReportName  FROM [Sheet1$] Group By ReportName, mid$(DateTime,1,10) ) Group By ReportName"

    rs.Open strSQL, cn

    Sheets("Sheet3").Select
    Sheets("Sheet3").UsedRange.ClearContents
    Dim nRow As Long, nCol As Long
    nRow = 1
    For nCol = 1 To rs.Fields.Count
        Sheets("Sheet3").Cells(nRow, nCol) = rs.Fields(nCol - 1).Name
    Next nCol
    Do While Not rs.EOF
        nRow = nRow + 1
        For nCol = 1 To rs.Fields.Count
            Sheets("Sheet3").Cells(nRow, nCol) = rs.Fields(nCol - 1).Value
        Next nCol

        rs.MoveNext
    Loop

End Sub