Excel Time Card Round UP

0

8:00 to 9:06 = 1.0 hrs

8:00 to 9:07 = 1.25 hrs

8:00 to 9:22 = 1.5 hrs

8:00 to 9:37 = 1.75 hrs

8:00 to 9:52= 2.0 hrs

I need a formula for excel that rounds to the nearest quarter after the 7th minute in each quarter. I have used  =MROUND(D4-B4-C4,"0:15")*24 but this rounds up when it hits the 8th minute of the quarter. Thank you.

Answer
Discuss

Answers

0

The basic formula is

=MROUND(A1*24,0.25)/24

Here A1 holds the time to be rounded and the cell in which the formula resides is formatted as Time, as is A1.

Applied to the time you appear to want to round the formula might look like

=MROUND((D4-B4-C4)*24,0.25)/24 

If you wish to slant the rounding to round up after 7 minutes instead of 8, just add 1 minute to the time you are about to round. That would then look like this:-

=MROUND((D4-B4-C4+(1/1440))*24,0.25)/24 
Discuss

Discussion

I tried this formula and get decimals like 0.05208333333
arelymonique Sep 12, '17 at 9:43 pm
That's because the cell isn't formatted as Time. Change the NumberFormat to see the translation of that value into an expression of time. I wrote about this in greater detail the other day. https://www.teachexcel.com/talk/2038/time-formula-to-calculate-end-time
Variatus (rep: 1815) Sep 12, '17 at 10:53 pm
Thank you!! So much help!!!!
arelymonique Sep 12, '17 at 11:59 pm
I'm glad to help. Please accept my reply. It helps others who look for answers, and it helps build my reputation on this forum. :-) Thank you.
Variatus (rep: 1815) Sep 13, '17 at 1:17 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login