Floor Function for Time Interval

[bruce21]

Thanks to all who respond.

I am using the FLOOR function with a 2:30 time interval to group my time cells but because of the rounding it is not 100% accurate for grouping.

See the bold items below. I want to group the records that are within 2:30 from the previous record.

6:12:31 0.2587
6:12:35 0.2587
6:12:40 0.2587
6:16:12 0.2604
6:18:08 0.2622
6:24:50 0.2656
6:24:52 0.2656
6:26:46 0.2674
6:30:10 0.2708
6:30:19 0.2708
6:30:24 0.2708

Is there another more accurate way?

Thanks, BD

 

[StephenCrump]

The numbers posted look fine to me, based on what you've described:

I am using the FLOOR function with a 2:30 time interval ....

But what is it you're actually trying to do?

I want to group the records that are within 2:30 from the previous record.

It sounds like your formula should be calculating time differences between cells?

For the data you've posted, what should the results be?

 

[bruce21]

The floor function should convert the numeric to within 2:30 minutes of each other. The example above shows it does not convert 100% of the time. This is what I was expecting to see.

6:12:31 0.2587
6:12:35 0.2587
6:12:40 0.2587

6:16:12 0.2604
6:18:08 0.2604

6:24:50 0.2656
6:24:52 0.2656
6:26:46 0.2656

6:30:10 0.2708
6:30:19 0.2708
6:30:24 0.2708

 

[StephenCrump]

Assuming your times are in A1:A11, I can replicate your results using:

B1: =FLOOR(A1,2.5/1440)
B2: =IF(A2-INDEX(A$1:A1,MATCH(B1,B$1:B1,))>=2.499/1440,FLOOR(A2,2.5/1440),B1) copy down

Is this what you're looking for? It means that any particular time may be "rounded" differently depending on the distribution of other times in its "group"?

I have assumed all your times will have integral numbers of seconds (?) and used 2.499 to avoid binary floating-point rounding discrepancies.

 

[bruce21]

What does the 1440 represent?

 

[StephenCrump]

What does the 1440 represent?

=24*60 minutes in a day.

So 2.5 / 1440 is 00:02:30 as required.