How can I round an hour to the nearest 1/4 hour?
I am trying to set-up a program to use to do my timesheets for work
electronically... I have the excel copy of the sheet directly from the company. I need to be able to convert the differences between a time so that it shows up with the hour worked and to the nearest 1/4 hour. For example I need 7:31am to 11:10am to show up as 3.75, because it is 3 hours and 39 minutes and the 39 goes to 45 after. I figured out how to get it to do 3:39, but then I need it rounded to the 49 and set to show up as .75. Any ideas would be great. Thanks--Ms Chewie |
Start time in A1
End time in B1 Hours worked in C1 =B1-A1 Round to ¼ hour and convert to decimal in D1: =ROUND(C1*96,0)/96*24 -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Ms Chewie" <Ms wrote in message ... I am trying to set-up a program to use to do my timesheets for work electronically... I have the excel copy of the sheet directly from the company. I need to be able to convert the differences between a time so that it shows up with the hour worked and to the nearest 1/4 hour. For example I need 7:31am to 11:10am to show up as 3.75, because it is 3 hours and 39 minutes and the 39 goes to 45 after. I figured out how to get it to do 3:39, but then I need it rounded to the 49 and set to show up as .75. Any ideas would be great. Thanks--Ms Chewie |
Forgot to mention that D1 should be formatted as General.
-- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "RagDyer" wrote in message ... Start time in A1 End time in B1 Hours worked in C1 =B1-A1 Round to ¼ hour and convert to decimal in D1: =ROUND(C1*96,0)/96*24 -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Ms Chewie" <Ms wrote in message ... I am trying to set-up a program to use to do my timesheets for work electronically... I have the excel copy of the sheet directly from the company. I need to be able to convert the differences between a time so that it shows up with the hour worked and to the nearest 1/4 hour. For example I need 7:31am to 11:10am to show up as 3.75, because it is 3 hours and 39 minutes and the 39 goes to 45 after. I figured out how to get it to do 3:39, but then I need it rounded to the 49 and set to show up as .75. Any ideas would be great. Thanks--Ms Chewie |
|
Hi!
A1 = 7:31 AM B1 = 11:10 AM =ROUND(((B1-A1)*24)/0.25,0)*0.25 = 3.75 Biff -----Original Message----- I am trying to set-up a program to use to do my timesheets for work electronically... I have the excel copy of the sheet directly from the company. I need to be able to convert the differences between a time so that it shows up with the hour worked and to the nearest 1/4 hour. For example I need 7:31am to 11:10am to show up as 3.75, because it is 3 hours and 39 minutes and the 39 goes to 45 after. I figured out how to get it to do 3:39, but then I need it rounded to the 49 and set to show up as .75. Any ideas would be great. Thanks--Ms Chewie . |
Thanks!
"Biff" wrote: Hi! A1 = 7:31 AM B1 = 11:10 AM =ROUND(((B1-A1)*24)/0.25,0)*0.25 = 3.75 Biff -----Original Message----- I am trying to set-up a program to use to do my timesheets for work electronically... I have the excel copy of the sheet directly from the company. I need to be able to convert the differences between a time so that it shows up with the hour worked and to the nearest 1/4 hour. For example I need 7:31am to 11:10am to show up as 3.75, because it is 3 hours and 39 minutes and the 39 goes to 45 after. I figured out how to get it to do 3:39, but then I need it rounded to the 49 and set to show up as .75. Any ideas would be great. Thanks--Ms Chewie . |
All times are GMT +1. The time now is 12:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com