Function MaxTimes(rTimes As Range, rData As Range)
Dim lCol As Long, strAux As String, bBreak As Boolean
Dim sFirst As String, sLast As String
'sFirst and sLast are used to store the first and
'the last Time, respectively, of the interval whose values = Max
'bBreak is used to check if the sequence of Max was breaked (cell<>Max)
'Loop through columns of the range rTimes
For lCol = 1 To rTimes.Columns.Count
'Checks if cell in this column=Max
If rData.Cells(1, lCol) = Application.Max(rData) Then
'If first value doesn1t exist yet, create it: New interval
If sFirst = "" Then
sFirst = Format(rTimes.Cells(1, lCol), "h:mm")
'makes the last value= first value, just in case there is only
'one value = Max in the interval
sLast = sFirst
Else
'there are more than one value=Max in the interval, so adjusts the last value
sLast = Format(rTimes.Cells(1, lCol), "h:mm")
End If
'check if last column was reached.
'If so, break=true to force calculation on the if below
'if not, there is no break since cell=Max: break = False
bBreak = (lCol = rData.Columns.Count)
Else
'a break in sequence of max values occured
bBreak = True
End If
'if break (cell<>Max) checks if sFirst exists
'if so creates the string
If bBreak And sFirst <> "" Then
strAux = strAux & ", " & sFirst & IIf(sFirst = sLast, "", "-" & sLast)
'reset sFirst and sFast: it's the end of a sequence of Max values
sFirst = ""
sLast = ""
End If
Next lCol
MaxTimes = Mid(strAux, 3)
End Function
