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

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 quarter(:25,
: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!!!!!

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

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

"RosaR" wrote:

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 quarter(:25,
: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!!!!!

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

hi,

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

as there are 95 quarter hours in a day

Cheers

Darren

"RosaR" wrote:

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 quarter(:25,
: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!!!!!

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

: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))




RosaR wrote:

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 quarter(:25,
: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!!!!!

--

Dave Peterson

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

One way:

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

or

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


In article ,
RosaR wrote:

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 quarter(:25,
: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!!!!!

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

=mround(K5,1/96)
--
David Biddulph

"RosaR" wrote in message
...
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 quarter(:25,
: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!!!!!

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

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 ,RosaR
JM wrote:

?? 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 quarter(:25, :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:
Formatcellsnumberfraction.

James Silverton
Potomac, Maryland

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