Forum Discussion
Excel rounding time to the next increment of 6 seconds
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)
- Thank you, I was finally able to make the ceiling formula work. Can you advise how i would then multiply the hours:minutes:seconds times a rate to get the billable dollar figure ( 123 hours 15 minutes 25 seconds at a billing rate of 0.008. I can't get it to convert to a dollar figure, only hours and minutes.
Is that 0.008 per hour? If so, use
=time*24*0.008
and format the cell with the formula as number or as currency.