Adjusting time data to nearest ten minute block
 

Hi,

I have a column of time data in hh:mm format which I need to adjust.
I need the time to be changed to the nearest ten minutes so 13:01
would change to 13:00 and 13:05 would round up to 13:10. Can you
help? Would I need to try and if statement with Right(A1,1) etc?

Thanks,
Matt

Adjusting time data to nearest ten minute block
 

Hi,

Do it like this

=ROUND(A1*(24*6),0)/(24*6)

where 24*6 equals the number of 20 minute periods in a day, there are 6 ten
minute periods in an hour. Changing the to 24*4 makes it work for 15 minutes
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"MJKelly" wrote:

Hi,

I have a column of time data in hh:mm format which I need to adjust.
I need the time to be changed to the nearest ten minutes so 13:01
would change to 13:00 and 13:05 would round up to 13:10. Can you
help? Would I need to try and if statement with Right(A1,1) etc?

Thanks,
Matt
.

Adjusting time data to nearest ten minute block
 

I meant

where 24*6 equals the number of 10 minute periods in a day
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Hi,

Do it like this

=ROUND(A1*(24*6),0)/(24*6)

where 24*6 equals the number of 20 minute periods in a day, there are 6 ten
minute periods in an hour. Changing the to 24*4 makes it work for 15 minutes
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"MJKelly" wrote:

Hi,

I have a column of time data in hh:mm format which I need to adjust.
I need the time to be changed to the nearest ten minutes so 13:01
would change to 13:00 and 13:05 would round up to 13:10. Can you
help? Would I need to try and if statement with Right(A1,1) etc?

Thanks,
Matt
.