• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • 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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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