Summarize by hours and status

[Mike_M-rpbo]

Our organization runs a bird banding stations and we record our data in excel (2010). One component that we'd like to capture is the number of net hours (the number of hours each net is open) for each station.

I would like to summarize the following data, tallying the hours by status and date.

Data:

Timestamp	Program	Date	Duration	Net 1	Net 2	Net 3	Net 4
20210914 06:05	Migration	20210914	1:00	Wind	Wind	Open	Open
20210914 07:05	Migration	20210914	5:00	Open	Open	Open	Open
20210914 12:05	Migration	20210914	0:00	Closed	Closed	Closed	Closed
20210915 06:05	Migration	20210915	2:00	Rain	Rain	Rain	Rain
20210915 08:05	Migration	20210915	4:00	Open	Open	Open	Open
20210915 12:05	Migration	20210915	0:00	Closed	Closed	Closed	Closed
20210915 20:00	Owls	20210915	6:00	Open	Open		Open
20210916 02:00	Owls	20210915	0:00	Closed	Closed		Closed

 
Desired report (for the passerine program)

Date	Open	Rain	Wind	Total
2021/09/14	22.0	0.0	2.0	24.0
2021/09/15	16.0	8.0	0.0	24.0
Total	38.0	8.0	2.0	48.0

Background:

One other complicating factor is that we run multiple programs at each station and need to tabulate the net hours per program.

The structure I've laid out is an excel table with the following columns:
• Timestamp
• Protocol (synonymous with program)
• Date (calculated column based on timestamp, it's not simply the date portion as one of our programs spans midnight and we want to treat each session as a single "day")
• Duration - Calculated column. The duration represented by this record.
• One column per net with the net status (constrained by a range) for the time interval that starts with this entry. There will be an entry marking the close of each day. I'm open to a different structure but it has to be easy for the staff to enter. I can create forms to assist with data entry.

What I'd like to see is basically a pivot table with the program as the page filter, date in the rows, the status as the columns and the cells being the total time the nets were in the particular status on the date. This is the sum of count of the number of nets with the status times the duration.

Looking back at the data the open net hours for September 14 is calculated as:
Row 1: 1:00 * 2 (count of "Open" in row 1)
Row 2: 5:00 * 4

I have sample data but don't see an option to attach it.

Thanks for any help

Mike

[#2]

@Mike_M-rpbo 

I came up with a solution, I'll post it here in case anyone in the future has a similar need. I'm sure a more elegant solution could be found. In this case I had to have a solution that works in Excel 2010 so some of newer excel functions were not available.

More generally this problem can be thought of as having a set of objects that are in any one of multiple states throughout the day. The objects can be used for multiple projects but the projects do not overlap in terms of time. The goal is to report on how long each object is in each of the states both daily and over a period of time.

The solution I came up with is two excel worksheets, one to store the data and a second for the report.

The data is structured as I outlined in my initial post. The report is structured as follows:

Cell B1 contains a dropdown list of the projects.

The first column contains the unique dates starting in cell A4. The array formula for this column is:

=IfError(Index(NetHourDetails[Date only],Match(0,CountIf($A$3:A3, NetHourDetails[Date only]),0)),"")

The states are in the third row starting in cell B3. This is also an array formula, it is:

=If(Column()-1>Rows(NetStatus),"", Transpose(NetStatus))

The array formula used for the cells in the report is:

=If(B$3="",0,1)*Sum(If(NetHourDetails[[Net 1]:[Net 15]]=B$3,1,0)*If(NetHourDetails[Date only]=$A4,1,0)*If(NetHourDetails[Protocol]=$B$1,1,0)*NetHourDetails[Hours])

NetStatus is a named range with all the net status values.

Any comments/suggestions are welcome.

Mike

[#3]

@Mike_M-rpbo Here's another formula that doesn't require arrays while adhering to the constraints of office version 2010.   

Just in case you're interested, despite already having solved your challenge yourself.  :) 

=SUMPRODUCT((NetHourDetails[[Net 1]:[Net 15]]=B$3)*(NetHourDetails[Date only]=$A14)*(NetHourDetails[Protocol]=$B$1)*(NetHourDetails[Hours]))