Forum Discussion
Excel rounding time to the next increment of 6 seconds
rounding to the next 6 second increment to calculate billing for a call, leave if multiple of 6 already.
time is displayed in minutes and seconds 00:00. Need to round up to the next 6 second increment unless it is already a multiple of 6. EX: if 00:15 seconds it would round to 00:18 and display seconds only in a new column, if 00:06, 00:12 or 00:18 it would remain the same and move the current value to the new column as is.
8 Replies
With a time in T1, the following formula rounds the time up to the next multiple of 6 seconds:
=CEILING(T1,TIME(0,0,6))
HansVogelaar , thank you for the response. However, it seems to only bring the result of zero. I have a column with time in minutes and seconds that need to round up to the next 6 second increment unless it is already a multiple of 6.
01:19 needs to become 01:24 in a new column.
01:18 would need to remain the same as there would be no rounding since 18 is a multiple of 6.
01:58 would need to round to 2:00
My apologies if I am not being clear.
If you enter 01:19 in a cell, Excel interprets it as 1 hour, 19 minutes and 0 seconds.
To enter 1 minutes and 19 seconds, enter 00:01:19 (or 0:1:19)
