Rounding up to nearest quarter, (:25, :50, :75, :00)

[Guest]

I am working on creating a timesheet in excel, I've got most of it figured
out, but now I need to convert my total hours to the nearest quarter25,
:50, :75, :00). For example: cell K5=7:50 (7 hours + 50 minutes), I believe
the nearest quarter hour should be 7:75 or 7.75, how can I get excel to do
this.

Plus at the end of the week I need to add up all the hours, with minutes
converted to nearest quarter. Please help me!!!!!

 

[Guest]

with time in A1:

in B1 formatted as NUMBER with 2 decimal places

=INT(A1*24)+INT(MOD(A1*24,1)/0.25)*0.25

 

[Guest]

hi,

the formula is =ROUND({time}*96,0)/96

as there are 95 quarter hours in a day

Cheers

Darren

 

[Dave Peterson]

:00, :15, :30, :45 would be the quarter hours.

With the time in A1, you could use this formula in B1 (say)
=ROUND(A1/TIME(0,15,0),0)*TIME(0,15,0)

If you wanted to always to go the next quarter hour:
=CEILING(A1,TIME(0,15,0))

If you always wanted to chop to the previous quarter hour:
=FLOOR(A1,TIME(0,15,0))

 

[JE McGimpsey]

One way:

K5: 7:50
L5: =ROUND(K5*96,0)/96 ===> 7:45

or

L5: =ROUND(K5*96,0)/4 ==> 7.75

 

[David Biddulph]

=mround(K5,1/96)

 

[James Silverton]

JE wrote on Wed, 30 May 2007 14:21:55 -0600:

JM> K5: 7:50
JM> L5: =ROUND(K5*96,0)/96 ===> 7:45

JM> or

JM> L5: =ROUND(K5*96,0)/4 ==> 7.75

JM> In article
JM> <[email protected]>,RosaR

??>> I am working on creating a timesheet in excel, I've got
??>> most of it figured out, but now I need to convert my total
??>> hours to the nearest quarter25, :50, :75, :00). For
example:
??>> cell K5=7:50 (7 hours + 50 minutes), I believe the nearest
??>> quarter hour should be 7:75 or 7.75, how can I get excel
??>> to do this.
??>>
??>> Plus at the end of the week I need to add up all the
??>> hours, with minutes converted to nearest quarter. Please
??>> help me!!!!!

You might also like to display the result as quarters:
Format>cells>number>fraction.

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.verizon.not