Calculate time intervals in minutes of sequential data

rdupree · Dec 15, 2021

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.

rdupree · Dec 29, 2021

Marcelo Branco said:
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.

Marcelo this is what I am looking for! Thank you very much! Just a few minor tweaks if that's ok. Offthelip is also correct. The numbers I used for examples of the output data I am looking for need 15min added to each, except where they show just one single time value. To shed more light, the times in column B represent 15min time intervals that average data (not attached) over that 15min interval. Not a time stamp.

For example, 7:00:00 = 7:00:00 to 7:15:00,
7:15:00 = 7:15:00 to 7:30:00,
7:30:00 = 7:30:00 to 7:45:00,
and so on...

So a time series from 7:00:00 - 7:30:00 actually represents 45min of time.

My mistake I should have been more clear.

Example sheet is below.

Is there a "clean" way to make that addition of 15min to each output given your formula. I can't say thank you enough for both of your help! Much appreciated.

2021 Mabank Interval Data_5MW and above Calcs.xlsx

A

B

C

5525

8/10/2021