Solved

# working out min and max time entries in excel.

Posted on 2012-09-20
256 Views
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

LVL 24

Accepted Solution

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 Comment

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

LVL 24

Expert Comment

Does the first pivot table work on the first of the two workbooks?
Does the VBA function created help?
0

## Featured Post

Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.