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

SavGDK · #1 04-06-2018, 08:37 AM

I have a large data set with multiple occurrences of a value of 505, I need to find the time the value 505 occurred and subtract the dates to get the total time the value 505 occurred.
I also need to do a similar subtraction to get the hours the column labeled PV was equal to or greater than 490, sometimes that occurs one value below the last occurrence of 505 in the SP column. Please see the attached workbook hopefully it will be more clear, the value in green is the dates I would need to subtract to get the hours the system was greater than or equal to 490. I have over 31,000 lines to get through so any help to automate this would be appreciated.

My spreadsheet also shows some formulas I have tried but with no luck.

Thank you,

dan

Pecoflyer · #2 04-06-2018, 09:27 AM

So, subtract the end and begin time of a consecutive series of 505 ?
About the PV it is not so clear. Are the values to be used independent of 505 ? Where should the "green zone" stop?
Are you willing to give a different name to each series?

NoSparks · #3 04-06-2018, 12:42 PM

@ SavGDK

It appears to me there's going to be over 300 results for each of SP and PV, how and where are you wanting those results?

SavGDK · #4 04-06-2018, 12:53 PM

Hello,

Thank you for the reply, you are correct for the first time I need to subtract the end and begin time, at each series beginning with 505.
Regarding the PV column, the values of interest always occur within the same range of the 505 series, the one difference is up to 2 instances after the last 505 value is changed to 250, the PV value could still be above 490, so I would need to subtract that date value from the first date value where the PV was greater than 490.

Hope that clears it up.

SavGDK · #5 04-06-2018, 12:55 PM

Quote:

Originally Posted by NoSparks

@ SavGDK

It appears to me there's going to be over 300 results for each of SP and PV, how and where are you wanting those results?

I have been putting the results in column E at the first occurrence of each 505 series. Right now that seems easiest because I need to capture the first date that the value occurred anyway.

Thank you,

dan

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.

SavGDK · #7 04-09-2018, 04:34 AM

Thank you all for the help. I have been able to automate this process.

Thanks again.

dan

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