How to count time if value is present?

[rageagainstjg]

Hello all,

I am attempting to calculate how long consecutive occurrence instances happened. So with that being said please look at this link and know that column “D” is the one I need the formula for

https://imgur.com/a/j9NItib

Any help would be SO WONDERFUL as figuring it out manually stinks!

 

[rageagainstjg]

I can't figure out how to edit my original post and I needed to update the question. So here is my original question but only expanded.

Hello all,

I am attempting to calculate how long consecutive occurrence instances happened, and the time period of occurrence. So with that being said please look at this link and know that column “D” and "E" are the ones I need the formula for

https://imgur.com/a/DR7ebCq

Any help would be SO WONDERFUL as figuring it out manually stinks!

 

[Peter_SSs]

I can't figure out how to edit my original post

Once you have made some more posts you will be able to edit your posts for 10 minutes after posting.

Any help would be SO WONDERFUL

Help would likely come faster if ..

a) We knew what Excel version you are using since different versions have different functions available. Therefore I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

b) Helpers could copy your sample data to test rather than having to manually type it all out from a picture. MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Peter_SSs]

.. different versions have different functions available.

For example, if you have the XLOOKUP function ..

22 10 25.xlsm
	A	B	C	D	E
1	time	ynw	Count	Tot time	Period
2	12:01	y	1
3	12:02	y	2	0:01	12:01 - 12:02
4	12:03	n	1
5	12:04	n	2
6	12:05	n	3	0:02	12:01 - 12:05
7	12:06	y	1
8	12:07	y	2	0:01	12:01 - 12:07
9	12:08	w	1
10	12:09	w	2
11	12:10	w	3
12	12:11	w	4
13	12:12	w	5
14	12:13	w	6	0:05	12:01 - 12:13
15	12:14	y	1
16	12:15	y	2	0:01	12:01 - 12:15
17	12:16	n	1
18	12:17	y	1
19	12:18	y	2	0:01	12:01 - 12:18
Consecutive

Cell Formulas
Range	Formula
C2:C19	C2	=IF(B2=B1,C1+1,1)
D2:D19	D2	=IF(C3>=C2,"",A2-XLOOKUP(1,C$2:C2,A$2:A2,,,-1))
E2:E19	E2	=IF(D2="","",TEXT(XLOOKUP(1,C$2:C2,A$2:A2),"hh:mm")&TEXT(A2," - hh:mm"))