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

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
broadcastwarehouse
Asked:
broadcastwarehouse
  • 2
1 Solution
 
SteveCommented:
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

Open in new window


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
 
broadcastwarehouseAuthor 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
 
SteveCommented:
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.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now