Time rounding help
I am working on a time sheet that I need to tweak slightly, currently
I have a time in and time out log: In: 6:58 Out 17:36 I am trying to round these numbers to the nearest hour 6:58 to 7am, however 17:36 needs to round down to 17:00. The formula I am curently using is: =TIME(HOUR(B9),MROUND(MINUTE(B9),$M$1),0) The M references the rounding #. It is currently set at 60. Scott |
Time rounding help
=ROUND(A1*24,0)/24
-- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Sanch" wrote in message oups.com... I am working on a time sheet that I need to tweak slightly, currently I have a time in and time out log: In: 6:58 Out 17:36 I am trying to round these numbers to the nearest hour 6:58 to 7am, however 17:36 needs to round down to 17:00. The formula I am curently using is: =TIME(HOUR(B9),MROUND(MINUTE(B9),$M$1),0) The M references the rounding #. It is currently set at 60. Scott |
Time rounding help
This works on the first portion of the formula, but I need to orund
down the second #, Do you know how I can accomplish this? I need to turn 17:36 into 17:00. Do you know why when I use the following formula =TIME(HOUR(B9),MROUND(MINUTE(B9),$M$1),0) it does not orund to the nearest zero. 17:15 become 17:00, but 6:58 become 6:00. Thanks, Scott Bob Phillips wrote: =ROUND(A1*24,0)/24 -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Sanch" wrote in message oups.com... I am working on a time sheet that I need to tweak slightly, currently I have a time in and time out log: In: 6:58 Out 17:36 I am trying to round these numbers to the nearest hour 6:58 to 7am, however 17:36 needs to round down to 17:00. The formula I am curently using is: =TIME(HOUR(B9),MROUND(MINUTE(B9),$M$1),0) The M references the rounding #. It is currently set at 60. Scott |
Time rounding help
Why, you originally said ROUND not ROUNDDOWN.
=ROUNDDOWN(A1*24,0)/24 or =FLOOR(A1,1/24) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Sanch" wrote in message oups.com... This works on the first portion of the formula, but I need to orund down the second #, Do you know how I can accomplish this? I need to turn 17:36 into 17:00. Do you know why when I use the following formula =TIME(HOUR(B9),MROUND(MINUTE(B9),$M$1),0) it does not orund to the nearest zero. 17:15 become 17:00, but 6:58 become 6:00. Thanks, Scott Bob Phillips wrote: =ROUND(A1*24,0)/24 -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Sanch" wrote in message oups.com... I am working on a time sheet that I need to tweak slightly, currently I have a time in and time out log: In: 6:58 Out 17:36 I am trying to round these numbers to the nearest hour 6:58 to 7am, however 17:36 needs to round down to 17:00. The formula I am curently using is: =TIME(HOUR(B9),MROUND(MINUTE(B9),$M$1),0) The M references the rounding #. It is currently set at 60. Scott |
All times are GMT +1. The time now is 06:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com