Floor Function for Time Interval

bruce21

New Member
Joined
Oct 25, 2018
Messages
13
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top