Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round Question
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round Question
On May 20, 11:13*am, carl wrote:
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round Question
On May 20, 2:14*pm, joeu2004 wrote:
On May 20, 11:13*am, carl wrote: 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. I just want to stand up and clap. :) Pete |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting question with Round | Excel Worksheet Functions | |||
Round Up and Round Down Time | Excel Worksheet Functions | |||
Round-Up question | Excel Discussion (Misc queries) | |||
Round-Up question | Excel Discussion (Misc queries) | |||
How do I ROUND() round off decimals of a column dataset? | Excel Worksheet Functions |