Adjusting time data to nearest ten minute block

M

MJKelly

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
 
M

Mike H

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.
 
M

Mike H

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top