Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!!!!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!!!!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!!!!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!!!!! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!!!!! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rounding to the nearest quarter point? | Excel Discussion (Misc queries) | |||
Rounding to the nearest quarter (decimal) in a Pivot table | Excel Discussion (Misc queries) | |||
how do I round 3.30 to the nearest quarter. 3.30=3.25 | Excel Worksheet Functions | |||
Rounding results to nearest quarter (in decimals) | Excel Discussion (Misc queries) | |||
Rounding number to the nearest quarter | Excel Worksheet Functions |