Rounding to fixed values

  • Thread starter Thread starter Gilbert De Ceulaer
  • Start date Start date
G

Gilbert De Ceulaer

I have a problem.
I have to round (downwards) times to a fixed value of either 12, 27,42 or 57 minutes.
E.g; 16:04 should become 15:57, 16:12 should remain 16:12, and even 16:26 should become 16:12
How ?
Thanks in advance,
Gilbert
 
Hi Gilbert
first: just curious: for that business purpose do you need such a
rounding???

For your solution:
- enter the values 12, 27, 42, 57 in this order in the range A1:A4
(sorted ascending)
- enter your time to be rounded in C1
- use the following formula
=IF(MINUTE(C1)<12,TIME(HOUR(C1)-1,57,0),TIME(HOUR(C1),VLOOKUP(MINUTE(C1
),$A$1:$A$4,1),0))
 
One way:

=ROUND((A1+"0:3")/"0:15",0)*"0:15"-"0:3"


or, converting times to decimals and combining terms:

=(ROUND(A1*96+0.2,0)-0.2)/96
 
Oops, I answered my own question rather than yours. Try:


=FLOOR((A1+"0:3","0:15")-"0:3"
 
Back
Top