rounding

ssjerauld · Mar 28th, 2002, 07:40 PM

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.

Dave Hawley · Mar 28th, 2002, 08:11 PM

Hi Scott

I haven't given this a thorough test, but

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

Should do the trick

Yogi Anand · Mar 28th, 2002, 08:30 PM

Quote:

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!

ssjerauld · Mar 28th, 2002, 08:50 PM

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

Dave Patton · Mar 28th, 2002, 08:52 PM

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.

Dave Hawley · Mar 28th, 2002, 08:55 PM

Hi ssjerauld

Are you now saying the time is not in 24 hour time?

Dave Hawley · Mar 28th, 2002, 08:57 PM

If so try

=MROUND(C6,0.25)

ssjerauld · Mar 28th, 2002, 09:12 PM

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.

Dave Patton · Mar 28th, 2002, 09:16 PM

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

ssjerauld · Mar 28th, 2002, 09:26 PM

Dave,
We have a winner! =ROUND(B65*4,0)/4 did the trick.
THANKS EVERYONE !

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 28th, 2002, 07:40 PM	#1
ssjerauld New Member Join Date: Mar 2002 Location: Central NY(not the park) Posts: 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.

Mar 28th, 2002, 08:11 PM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Scott I haven't given this a thorough test, but =MAX(FLOOR(D7,0.0104166666666667),CEILING(D7,0.0104166666666667)) Should do the trick

Mar 28th, 2002, 08:50 PM	#4
ssjerauld New Member Join Date: Mar 2002 Location: Central NY(not the park) Posts: 14	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

Mar 28th, 2002, 08:52 PM	#5
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	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.

Mar 28th, 2002, 08:55 PM	#6
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi ssjerauld Are you now saying the time is not in 24 hour time?

Mar 28th, 2002, 08:57 PM	#7
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	If so try =MROUND(C6,0.25)

Mar 28th, 2002, 09:12 PM	#8
ssjerauld New Member Join Date: Mar 2002 Location: Central NY(not the park) Posts: 14	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.

Mar 28th, 2002, 09:16 PM	#9
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	To round in .25 increments on decimal numbers not hours =ROUND(B654,0)/4 Revise reference as necessary. or back to hours converted to decimal =MROUND(C2,"00:15:00")24

Mar 28th, 2002, 09:26 PM	#10
ssjerauld New Member Join Date: Mar 2002 Location: Central NY(not the park) Posts: 14	Dave, We have a winner! =ROUND(B65*4,0)/4 did the trick. THANKS EVERYONE !