rounding time

J

johnfli

I have a formula in a cell that calculates the difference of two time
entries.
Sometimes I may have an answer of 5:05 (meaning 5 hours and 5 min.)
Now I need to round to the nearest 15 min. So my about example would need
to be rounded to 5:00 (meaning 5 hours)
and 5:08 would be rounded to 5:15

Thanks
 
J

johnfli

Never mind all, I got it
for future refference, here it is

Normally, the time in this cell would have read 1:17, now it reads 1:15


=ROUND(SUM(N17-I17)*96,0)/96
 
J

JE McGimpsey

Note that the SUM() is superfluous:

=ROUND((N17-I17)*96,0)/96

works just as well.
 
N

Norman Harker

Hi John!

Here's another approach using Excel's time format:

=ROUND((N17-I17)/"00:15:00",0)*"00:15:00"

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

[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Top