Hi all
I have a report that I am trying to get excel to help me with:
TIME STAMP GATE STATUS
8:00:00 AM 0
9:00:00 AM 0
10:00:00 AM 0 "0 is open and 1 is closed"
11:00:00 AM 1
12:00:00 PM 0
1:00:00 PM 1
As you can see a gate position is being monitored by looking at its Boolean status:0 means the gate is open and 1 means it's closed. I would like to find a way to totalize the time that the gate was open. If we call B column the gate status, then cell B8 would be showing 4 hours that the gate was open for. This is only a sample. We usually run a 24 hours report so the column would be a little longer.
Any help would be appreciated.
Thanks a lot.
Hi Guys,
Can anybody take a look at this please.
Thanks a lot
PerhapsAnd adapt the range to your needs=COUNTIF(B2:B100,0)
Cheers - THE WARNING I RECEIVED WAS NOT JUSTIFIED
Thanks Pepe.
That formula only counts the number of cells that have a "0" in them nothing else.That 's the first step of my problem, once we know how many cells have a "0" , I need to to totalize the time that those cells had a zero in them.
Thanks anyway.
Hi minosar73,
A helping column in the attached might help. It simply adds the times from one event to the next. Then you will sum the times next to the 0 or open events.
I've also put a pivot table in the sheet to total them by date. I hope your 2003 version can use the Pivot Table.
One test is worth a thousand opinions.
Click the * below to say thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks