Round time to 15 minutes.

[Mike Klick]

I have a simple formula that subtracts h:mm from h:mm. I would like to round
that time if < 8 minutes down 15 minutes and if > 8 minutes up 15 minutes.

How do you do that?

Thanks,

Mike

 

[Kieran]

As there are 96 15 minute periods in a day, you could multiply the time
by 96, round it to the nearest 1 and then divide by 96 again.
Like =round(time*96,1)/96

 

[Norman Harker]

Hi Mike!

One way:
=ROUND(A16/"00:15",0)*"00:15"

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Norman Harker]

Hi Mike!

Since, like most, I'm allergic to hard coded formulas I'd prefer:

=ROUND(A1/B1,0)*B1
Where B1 contains the rounding time 0:15


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.