Calculate time intervals in minutes of sequential data

[rdupree]

Ok so I need help calculating time interval data that is sequential in nature. To preface, this time data has already been filtered on a variable that isn't shown and I don't believe is necessary for this exercise. You will notice from my color shading where the time intervals break and pick back up.

I have several thousand rows of time stamped data, and I need to calculate the time interval (in minutes) over each and every interval of time. I've shown an example below to illustrate.
Column 1 = Date
Column 2 = Time (color shaded)
Column 3 = Time Interval in Minutes (done by hand for show)
The time stamps are consistently on a 15 min interval.

Is there a formula or function that I can use in excel to recognize the sequence, count the "chunks" of time in column 2, and put it in column 3?

Any help would be appreciated. If you need any further information, please feel free to ask.

Thank you in advance.

 

[offthelip]

You can do it using functions with a few "helper" columns. I have put these in columns C to F you can easily move them:
In C2:

=(B2-B1)>TIMEVALUE("00:15:00")

In D2

=B2-B1

In E2

=IF(C2,TIMEVALUE("00:15:00"),E1+D2)

In F2

=IF(C3,E2,"")

Then copy them all down

 

[rdupree]

You can do it using functions with a few "helper" columns. I have put these in columns C to F you can easily move them:
In C2:

=(B2-B1)>TIMEVALUE("00:15:00")

In D2

=B2-B1

In E2

=IF(C2,TIMEVALUE("00:15:00"),E1+D2)

In F2

=IF(C3,E2,"")

Then copy them all down

Thank you for the suggestion. So I plugged in the functions as suggested and below is what I am getting returned in the calculated cells. This has been a big part of the issue I have had in dealing with adding up time intervals, as opposed to just numerical values. Is there a different function I can use or am I plugging something in wrong?

Assuming first column is "A", second column is "B", and so on...

Thank you again.

11/30/2020​	3:45:00	FALSE​	-1​	-1​
11/30/2020​	4:00:00	FALSE​	0​	-1​
11/30/2020​	4:15:00	FALSE​	0​	-1​
11/30/2020​	4:30:00	FALSE​	0​	-1​	-1​
12/11/2020​	7:15:00	TRUE​	0​	0​	0​
1/25/2021​	13:00:00	TRUE​	0​	0​
1/25/2021​	13:15:00	FALSE​	0​	0​
1/25/2021​	13:30:00	FALSE​	0​	0​	0​
1/25/2021​	14:00:00	TRUE​	0​	0​
1/25/2021​	14:15:00	FALSE​	0​	0​
1/25/2021​	14:30:00	FALSE​	0​	0​
1/25/2021​	14:45:00	FALSE​	0​	0​
1/25/2021​	15:00:00	FALSE​	0​	0​
1/25/2021​	15:15:00	FALSE​	0​	0​	0​
2/19/2021​	17:00:00	TRUE​	0​	0​
2/19/2021​	17:15:00	FALSE​	0​	0​	0​
2/24/2021​	20:15:00	TRUE​	0​	0​
2/24/2021​	20:30:00	FALSE​	0​	0​
2/24/2021​	20:45:00	FALSE​	0​	0​
2/24/2021​	21:00:00	FALSE​	0​	0​
2/24/2021​	21:15:00	FALSE​	0​	0​
2/24/2021​	21:30:00	FALSE​	0​	0​
2/24/2021​	21:45:00	FALSE​	0​	0​	0​
2/24/2021​	23:45:00	TRUE​	0​	0​
2/24/2021​	0:00:00	FALSE​	-1​	-1​
2/25/2021​	0:15:00	FALSE​	0​	-1​
2/25/2021​	0:30:00	FALSE​	0​	-1​
2/25/2021​	0:45:00	FALSE​	0​	-1​
2/25/2021​	1:00:00	FALSE​	0​	-1​
2/25/2021​	1:15:00	FALSE​	0​	-1​	-1​

 

[offthelip]

You need to format the columns as "time". Another possibility is that column B
might be text not a time value, if this is the case you need to convert it to timevalue

 

[rdupree]

You need to format the columns as "time". Another possibility is that column B
might be text not a time value, if this is the case you need to convert it to timevalue

I did check the formatting of that column, and column B is definitely formatted as "time". Is there anything else obvious I may be missing?

Thanks again

RD

 

[Marcelo Branco]

Considering your data sample in post #3 (see below), could you fill column C with the expected/desired results?

Pasta1
	A	B	C
1	Date	Time	Expected results
2	11/30/2020	03:45:00
3	11/30/2020	04:00:00
4	11/30/2020	04:15:00
5	11/30/2020	04:30:00
6	12/11/2020	07:15:00
7	01/25/2021	13:00:00
8	01/25/2021	13:15:00
9	01/25/2021	13:30:00
10	01/25/2021	14:00:00
11	01/25/2021	14:15:00
12	01/25/2021	14:30:00
13	01/25/2021	14:45:00
14	01/25/2021	15:00:00
15	01/25/2021	15:15:00
16	02/19/2021	17:00:00
17	02/19/2021	17:15:00
18	02/24/2021	20:15:00
19	02/24/2021	20:30:00
20	02/24/2021	20:45:00
21	02/24/2021	21:00:00
22	02/24/2021	21:15:00
23	02/24/2021	21:30:00
24	02/24/2021	21:45:00
25	02/24/2021	23:45:00
26	02/24/2021	00:00:00
27	02/25/2021	00:15:00
28	02/25/2021	00:30:00
29	02/25/2021	00:45:00
30	02/25/2021	01:00:00
31	02/25/2021	01:15:00
Plan6

M.

 

[offthelip]

Given Marcelo has kindly typed all the data into XL2BB I thought I would try it with my equations from post two. I got error because the values in row 1 were not numbers, this was sovled by typing FALSE into C2 and zero in D2 after copying the equations down , then formatting colemns D and E as time. So I sugest you compare your data to the Data that marcelo has typed in to see what the difference is.
PS Please use XL2BB to upload your data then it makes it much easier to help you

 

[rdupree]

Thank you for the tip. Here is a sample of the spreadsheet from XL2BB. As a note, in Column C, the summed time values in minutes are hand typed to use for an example.

Thank you again for the help.

2021 Mabank Interval Data_5MW and above Calcs.xlsx
	D	E	F
5525	8/10/2021	0:15:00	15
5526	8/10/2021	2:45:00
5527	8/10/2021	3:00:00
5528	8/10/2021	3:15:00
5529	8/10/2021	3:30:00
5530	8/10/2021	3:45:00
5531	8/10/2021	4:00:00
5532	8/10/2021	4:15:00	90
5533	8/10/2021	7:00:00
5534	8/10/2021	7:15:00
5535	8/10/2021	7:30:00
5536	8/10/2021	7:45:00
5537	8/10/2021	8:00:00
5538	8/10/2021	8:15:00
5539	8/10/2021	8:30:00
5540	8/10/2021	8:45:00
5541	8/10/2021	9:00:00	120
5542	8/10/2021	9:45:00	15
5543	8/10/2021	11:00:00
5544	8/10/2021	11:15:00
5545	8/10/2021	11:30:00
5546	8/10/2021	11:45:00
5547	8/10/2021	12:00:00
5548	8/10/2021	12:15:00
5549	8/10/2021	12:30:00
5550	8/10/2021	12:45:00
5551	8/10/2021	13:00:00
5552	8/10/2021	13:15:00	135
5553	8/10/2021	14:00:00
5554	8/10/2021	14:15:00
5555	8/10/2021	14:30:00	30
KW >5000 FILTERED

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E:E	Other Type	Color scale		NO

 

[Marcelo Branco]

Maybe something like this (in my example below data begin in A2 - adjust the formula)

21122021 Testes.xlsx
	A	B	C
1	Date	Time	Result
2	08/10/2021	00:15:00	15
3	08/10/2021	02:45:00
4	08/10/2021	03:00:00
5	08/10/2021	03:15:00
6	08/10/2021	03:30:00
7	08/10/2021	03:45:00
8	08/10/2021	04:00:00
9	08/10/2021	04:15:00	90
10	08/10/2021	07:00:00
11	08/10/2021	07:15:00
12	08/10/2021	07:30:00
13	08/10/2021	07:45:00
14	08/10/2021	08:00:00
15	08/10/2021	08:15:00
16	08/10/2021	08:30:00
17	08/10/2021	08:45:00
18	08/10/2021	09:00:00	120
19	08/10/2021	09:45:00	45
20	08/10/2021	11:00:00
21	08/10/2021	11:15:00
22	08/10/2021	11:30:00
23	08/10/2021	11:45:00
24	08/10/2021	12:00:00
25	08/10/2021	12:15:00
26	08/10/2021	12:30:00
27	08/10/2021	12:45:00
28	08/10/2021	13:00:00
29	08/10/2021	13:15:00	135
30	08/10/2021	14:00:00
31	08/10/2021	14:15:00
32	08/10/2021	14:30:00	30
Plan10

Cell Formulas
Range	Formula
C2:C32	C2	=IF(ROUND(MOD(B3-B2,1)*1440,0)<>15,IF(OR(ISNUMBER(C1),ROWS(C$2:C2)=1),MINUTE(B2),MOD(B2-INDEX(B$2:B2,IFERROR(MATCH(9.99E+307,C$1:C1),1)),1)*1440),"")

M.

 

[offthelip]

Firstly your manual calculations are inconsistent. line 5532 you show a sum of 90 when there are 7 15 minute intervals , while on line 5542 you show 15 after only 1 interval, you can't have it both ways.!!
very simlar to my original equations:
in F3:
=(E3-E2)>TIMEVALUE("00:16:00")
in G3
=E3-E2
in H3
=IF(F3,TIMEVALUE("00:15:00"),H2+G3)
in i3
=IF(F4,24*60*H3,"")
Column I is formatted as a number NOT time