How can I round an hour to the nearest 1/4 hour?

[Guest]

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

 

[RagDyer]

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!
==============================================

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

 

[RagDyer]

Forgot to mention that D1 should be formatted as General.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

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!
==============================================

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

 

[Ron Rosenfeld]

On Mon, 20 Dec 2004 18:59:02 -0800, Ms Chewie <Ms

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

To round your time:

=ROUND((B1-A1)/TIME(0,15,0),0)*TIME(0,15,0)

With your times in A1 and B1, the above formula will show 3:45 as a result. To
show it as 3.75, you multiply by 24 (and format the result as General or as
Number with 2 decimal places):

=24*ROUND((B1-A1)/TIME(0,15,0),0)*TIME(0,15,0)


--ron

 

[Biff]

Hi!

A1 = 7:31 AM
B1 = 11:10 AM

=ROUND(((B1-A1)*24)/0.25,0)*0.25

= 3.75

Biff

 

[Guest]

Thanks!

Hi!

A1 = 7:31 AM
B1 = 11:10 AM

=ROUND(((B1-A1)*24)/0.25,0)*0.25

= 3.75

Biff