Round to nearest quarter hour?

[ShellyB]

First of all, I am not very familiar with Excel lingo. Please go slow

I am trying to round to the nearest quarter hour on a payroll worksheet. I am using =MROUND (L2, 0.25). It will round to tenths, but not quarters. And stranglely enough, last week, my =MROUND formula rounded everything to the quarter. I added a password protected the sheet and the document has been viewing/modified in both 2007 and 2010. Could any of these factors coorupted the file? I copied and pasted the information to new worksheet, but I still have the same results.

I have been working on this for days!!! Any assistance you could provide would be greatly appreciated.

~ShellyB

 

[Jonmo1]

Welcome to the board...

What is actually in L2, a Time like 15:37:00 (3:37 PM),
or just a number representing hours like 15.68 or something like thiat?

 

[markmzz]

ShellyB,

Try this formula:

=MROUND(L2*24,0.25)/24

Markmzz

 

[ShellyB]

Thank you!

It would be the total hours worked for a week. Example: L2= 38.55 hours, which should transfer to 39.00 hours.

 

[Jonmo1]

Thank you!

It would be the total hours worked for a week. Example: L2= 38.55 hours, which should transfer to 39.00 hours.

Hang on....

If You're rounding to nearest quarter hour, how does 38.55 round to 39?
Wouldn't that round to 38.5?

Or does 38.55 actually represent 38 hours and 55 minutes...?

 

[ShellyB]

@MARKMZZ

I tried the "=MROUND(L2*24,0.25)/24" but it didnt round anything... it just copied over the same exact number from L2.

 

[ShellyB]

@JONMO1:

Yes, I am trying to convert actual time and minutes into quarters. Sorry for not clarifying that!!!

So, 38 hours and 55 minutes would be entered into L2 as 38.55, and I need to convert that number into rounding to the nearest quarter hour.

 

[Jonmo1]

Try

=MROUND(SUBSTITUTE(L2,".",":"),"0:15:0"+0)

 

[ShellyB]

@ JONMO1:

I copy & pasted: "=MROUND(SUBSTITUTE(L2,".",":"),"0:15:0"+0)" and with 38.55 being in L2, the results were 1.625....

 

[markmzz]

ShellyB,

Try this one:

=INT(E2)+IF((E2-INT(E2))>0.45,1,IF((E2-INT(E2))>0.3,0.45,IF((E2-INT(E2))>0.15,0.3,IF((E2-INT(E2))>0,0.15,0))))

Markmzz
<!-- / message --><!-- edit note -->