Thread: Round Question
View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
joeu2004 joeu2004 is offline
external usenet poster
 
Posts: 2,059
Default Round Question

Errata....

On May 20, 12:14*pm, I wrote:
If you still believe 930 is the desired answer, perhaps
you want truncate seconds (the last 2 digits). *In that
case, use the following formula:
=--TEXT(ROUND(TEXT(A1/100,"0\:00")*1440/5,0)*5/1440,"hmm")


Works up to 49 seconds. But to truncate 50-59 seconds as well, the
formula must be:

=--TEXT(ROUND(TEXT(INT(A1/100),"0\:00")*1440/5,0)*5/1440,"hmm")

In my mind, I was thinking of "0\:00" as "h:mm", which does truncate
seconds (usually). However, "0\:00" is simply an integer format,
which rounds any decimal fraction.