Search for last date and first date and subtract to get hours

NoSparks · #6 04-07-2018, 03:28 PM

Hello dan

Think you're going to need VBA for this.

If you filter your data for =505 then use something along the lines of the code below
you could calculate everything except your TimeB.

This assumes .areas(1) is just the header row and starts doing its thing at areas(2),
not ideal but maybe it will give you some ideas.

Code:

With filtRng.Columns(2).SpecialCells(xlCellTypeVisible)
    For i = 2 To .Areas.Count
        strAddress = .Areas(i).Address
        ar = Split(Replace(Mid(strAddress, 2), ":", ""), "$")
        TimeA = (ws.Cells(ar(3), "B") - ws.Cells(ar(1), "B")) * 24
        FirstAvg = WorksheetFunction.Average(.Areas(i).Offset(, 1))
        SecondAvg = WorksheetFunction.AverageIf(.Areas(i).Offset(, 1), ">=" & 490)
        '
        'write those variables to the sheet
        'the first 505 row of each area would be  .Areas(i).Rows(1).Row
        '
    Next i
End With

then you'd need to do the same sort of thing filtering for >=490 in column D and get TimeB the same way.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
subtract date and time and convert to decimal hours ?	DBenz	Excel	5	01-26-2017 05:38 AM
Formula for decimal hours from two date time entries	DBenz	Excel Programming	0	01-15-2015 05:52 AM
Search for date and then apply mutliple search criteria in huge dataset	maxtymo	Excel	2	12-01-2013 04:52 AM
Formula to subtract one month from due date field in reminder field	ghumdinger	Outlook	1	10-01-2011 12:09 AM
Date and time calcs excluding non-working hours	mrsatchmo	Excel	1	01-26-2011 08:50 AM