Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I round off times in Excel?
I am calculating times by dividing a distance by a speed. The results are in
cells formatted hh:mm but always seem to be rounded down so that the sum of the column may be several minutes out, compared to the times displayed. If I reformat the cells hh:mm:ss then I can see for example what was 01:32 displayed as 01:32:57. I want this to appear as 01:33. Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I round off times in Excel?
Try this =ROUND(A18*1440,0)/1440 Rounds down if 29 sec and rounds up 31 sec VBA Noob -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=562623 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I round off times in Excel?
Hi!
So you want to round to the nearest minute? =ROUND(A1/(1/1440),0)*1/1440 Or: =ROUND(A1/TIME(0,1,0),0)*TIME(0,1,0) Biff "Andrew" wrote in message ... I am calculating times by dividing a distance by a speed. The results are in cells formatted hh:mm but always seem to be rounded down so that the sum of the column may be several minutes out, compared to the times displayed. If I reformat the cells hh:mm:ss then I can see for example what was 01:32 displayed as 01:32:57. I want this to appear as 01:33. Any ideas? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I round off times in Excel?
Thanks very much
"Biff" wrote: Hi! So you want to round to the nearest minute? =ROUND(A1/(1/1440),0)*1/1440 Or: =ROUND(A1/TIME(0,1,0),0)*TIME(0,1,0) Biff "Andrew" wrote in message ... I am calculating times by dividing a distance by a speed. The results are in cells formatted hh:mm but always seem to be rounded down so that the sum of the column may be several minutes out, compared to the times displayed. If I reformat the cells hh:mm:ss then I can see for example what was 01:32 displayed as 01:32:57. I want this to appear as 01:33. Any ideas? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I round off times in Excel?
Thanks very much
"VBA Noob" wrote: Try this =ROUND(A18*1440,0)/1440 Rounds down if 29 sec and rounds up 31 sec VBA Noob -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=562623 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I round off times in Excel?
You're welcome!
Biff "Andrew" wrote in message ... Thanks very much "Biff" wrote: Hi! So you want to round to the nearest minute? =ROUND(A1/(1/1440),0)*1/1440 Or: =ROUND(A1/TIME(0,1,0),0)*TIME(0,1,0) Biff "Andrew" wrote in message ... I am calculating times by dividing a distance by a speed. The results are in cells formatted hh:mm but always seem to be rounded down so that the sum of the column may be several minutes out, compared to the times displayed. If I reformat the cells hh:mm:ss then I can see for example what was 01:32 displayed as 01:32:57. I want this to appear as 01:33. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
How do I nest a ROUND within a IF in Excel? | Excel Worksheet Functions | |||
How can i add times in Excel? | Excel Discussion (Misc queries) | |||
Adding/Averaging Times in Excel | Excel Discussion (Misc queries) | |||
How do I duplicate a sheet 20 times in an excel spreadsheet | Excel Worksheet Functions |