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

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

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