rounding

ssjerauld

New Member
Joined
Mar 27, 2002
Messages
14
I am trying to write a function to round a number from a specific cell up or down to the nearest quarter of an hour. Time is written in military 24hr format.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Hi Scott

I haven't given this a thorough test, but

=MAX(FLOOR(D7,0.0104166666666667),CEILING(D7,0.0104166666666667))

Should do the trick
 
Upvote 0
On 2002-03-28 18:40, ssjerauld wrote:
I am trying to write a function to round a number from a specific cell up or down to the nearest quarter of an hour. Time is written in military 24hr format.

Hi ssjerauld:
If time is in cell B4, use the following:

to round up to quarter hour use:
=CEILING(B4,0.25/24)

to round down to quarter hour use:
=FLOOR(B4,0.25/24)

Please post back if it works for you ... otherwise explain a little further and let us take it from there!
 
Upvote 0
A little more info. I may have misspoken, time is in 10ths & 100ths. Actual Function should round 10.12 to 10.00, 8.93 to 9.00, 9.21 to 9.25, 8.42 to 8.50 and so on on on.
Am using MS OFFICE EXCEL 97
Sorry & Thanks
 
Upvote 0
the following will round time to the nearest 15 minutes,
if you need it as a number just *24 it.

=MROUND(D18,"00:15:00")
=MROUND(D18,"00:15:00")*24

If this function is not available, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, you must enable it by using the Add-Ins command on the Tools menu.
 
Upvote 0
Dave,
Yes the time is still 24hr clock. 01.00=1:00AM, 24.50=12:30PM. However the cell that I am targeting is for the Dailt Totals which are normaly in the 8 to 14 hour range.
 
Upvote 0
To round in .25 increments on decimal numbers not hours

=ROUND(B65*4,0)/4

Revise reference as necessary.

or back to hours converted to decimal

=MROUND(C2,"00:15:00")*24
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top