Calculate total hours

[jvddavid]

Hi, I am trying to calculate the total hours used in a machine (Data below) however, I want to only count data that is within 15 minutes of each other. For instance if the machine was used at 15:17:52. and then again at 15:45:00. those 25 mins and some shouldn't count towards my total hours.
Any help is appreciated it.



2018-01-02 15:17:52.0000000
2018-01-02 15:17:53.0000000
2018-01-02 15:18:49.0000000
2018-01-02 15:19:00.0000000
2018-01-02 15:19:10.0000000
2018-01-02 15:21:32.0000000
2018-01-02 15:39:44.0000000
2018-01-02 15:43:11.0000000
2018-01-02 15:43:22.0000000
2018-01-02 15:50:31.0000000
2018-01-02 15:50:41.0000000
2018-01-02 15:50:53.0000000
2018-01-02 15:54:02.0000000
2018-01-02 15:55:19.0000000
2018-01-02 15:59:56.0000000
2018-01-02 16:00:05.0000000
2018-01-02 16:00:14.0000000
2018-01-02 16:05:04.0000000
2018-01-02 16:05:18.0000000
2018-01-02 16:05:19.0000000
2018-01-03 07:58:58.0000000
2018-01-03 07:58:59.0000000
2018-01-03 07:59:08.0000000
2018-01-03 07:59:16.0000000
2018-01-03 08:00:07.0000000
2018-01-03 08:17:25.0000000
2018-01-03 08:17:50.0000000
2018-01-03 08:21:04.0000000
2018-01-03 08:21:05.0000000
2018-01-03 08:21:13.0000000
2018-01-03 08:45:26.0000000
2018-01-03 08:45:52.0000000
2018-01-03 08:46:36.0000000
2018-01-03 09:01:56.0000000
2018-01-03 09:41:51.0000000
2018-01-03 09:42:03.0000000
2018-01-03 09:42:04.0000000
2018-01-03 09:42:17.0000000

[Bernie Deitrick]

Based on your example data set, what result would you want?

You could try this formula, array-entered using Ctrl-Shift-Enter, with your data in A1:A38 - note the one row offset of the ranges used.

=SUM(IF(A2:A38 - A1:A37 < TIME(0,15,0),A2:A38 - A1:A37))

[jvddavid]

I am not sure if that works. It just spits out a number but can't figure out how to relate that number back to time.

[Bernie Deitrick]

Format the cell with the formula for time: [h]:mm:ss would be best, depending on the length of time you expect.