• Status: Solved
• Priority: Medium
• Security: Public
• Views: 260

# working out min and max time entries in excel.

Hi

I have some data that is exported from an entry system.

I would like to be able to work out the eariest time that each card entry takes place for each card, the latest and the average of the card entries for that card across the data provided.

For example, if the data had one months worth of entries, I desire to see the aforementioned three things for each card (column H). This will enable us to see the person who was earliest in or latest leave in any period of data.

The dates are not important, only the times across the range of data.

I have attached the file for reference.
security.xlsx
0
• 2
1 Solution

Commented:
OK, there is an AverageIF function... but No in-build MaxIF or MinIF.

So I have written them for you and added them to the attached workbook.

Example attached using these formula:

``````Function MaxIF(CriteriaRange As Variant, SearchString As String, MaxRange As Variant) As Variant

Dim i As Boolean: i = False
Dim j As Long: j = 1
For Each cell In CriteriaRange
If cell.Value = SearchString Then
If i Then
If MaxIF < MaxRange(j, 1) Then MaxIF = MaxRange(j, 1)
Else
MaxIF = MaxRange(j, 1)
i = True
End If
End If
j = j + 1
Next cell

End Function
Function MinIF(CriteriaRange As Variant, SearchString As String, MinRange As Variant) As Variant
Dim i As Boolean: i = False
Dim j As Long: j = 1
For Each cell In CriteriaRange
If cell.Value = SearchString Then
If i Then
If MinIF > MinRange(j, 1) Then MinIF = MinRange(j, 1)
Else
MinIF = MinRange(j, 1)
i = True
End If
End If
j = j + 1
Next cell

End Function
``````

this can also be done easily using PivotTable.. example included this too.
I have added a group by date to the pivot table of the second file here.. this is to see single day clockings (this may be more relevant)
security.xlsm
security.xlsm
0

Author Commented:
Your pivot table doesnt work because it it using the dates and not just the times.
So 01/01/12 at 19:00 is showing up as earleir than 12/12/12 at 09:00 when 09:00 is the earliest. I only care for times, not days.
0

Commented:
Does the first pivot table work on the first of the two workbooks?
Does the VBA function created help?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.