Round Question

[carl]

My time data looks like this (formated as number)

93245
124316
135946

I would like a formula that will round the time to the nearest "5"
minutes. Results like this:

930
1245
1400

Thank you in advance.

 

[joeu2004]

My time data looks like this (formated as number)
93245
124316
135946
I would like a formula that will round the time
to the nearest "5" minutes. Results like this:
930
1245
1400

Ostensibly:

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

I will explain below. But note that that returns 935 for 93245, not
930. I believe 935 is indeed the correct result, since 3m is closer
to 2m45s then 2m.

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")

The double-negative converts text to numeric. The inner TEXT function
converts your hmmss representation to h:mm:ss (or h:mm). Multiplying
by 1440 converts Excel time to a decimal number of minutes. Note that
Excel time is represented as a fraction of a day; so 1 hour is 1/24, 1
minute is 1/1440, and 1 second is 1/86400. The expression
ROUND(...*5,0)/5 rounds to the nearest 5-minute multiple. Dividing by
1440 converts back to Excel time. And the outer TEXT converts back to
your hmm representation.

Note that you could replace 1440/5 with 288 and *5/1440 with /288.

 

[Pete]

Ostensibly:

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

I will explain below.  But note that that returns 935 for 93245, not
930.  I believe 935 is indeed the correct result, since 3m is closer
to 2m45s then 2m.

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")

The double-negative converts text to numeric.  The inner TEXT function
converts your hmmss representation to h:mm:ss (or h:mm).  Multiplying
by 1440 converts Excel time to a decimal number of minutes.  Note that
Excel time is represented as a fraction of a day; so 1 hour is 1/24, 1
minute is 1/1440, and 1 second is 1/86400.  The expression
ROUND(...*5,0)/5 rounds to the nearest 5-minute multiple.  Dividing by
1440 converts back to Excel time.  And the outer TEXT converts back to
your hmm representation.

Note that you could replace 1440/5 with 288 and *5/1440 with /288.

I just want to stand up and clap.
Pete

 

[joeu2004]

Errata....

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.