• Status: Solved
• Priority: Medium
• Security: Public
• Views: 398

# Rounding time

Hi there,

I am not sure if I will explain properly what I need, but here it goes.

I need to make an Excel 2010 formula that will round time (in decimal) to the closest increment.

For example:
Increment=15minutes
10.05h=10.00h
10.20h=10.25
10.45h=10.50
10.99h=11.00

Cheers,
Rene
0
ReneGe
• 5
• 2
• 2
2 Solutions

IT ConsultantCommented:
use this formula
=(ROUND((A1*1440)/15,0)*15)/1440

sample.xlsx
0

Commented:
It looks as your time is decimal hours, not hours:minutes.
To round to nearest 0.25 hours = 15 minutes, use
=MROUND(A1,0.25)
Then format to 2 decimals.
0

Author Commented:
Thanks helpfinder for your prompt response :)

Here is a list of times that needs to be rounded up:
8.70
12.53
13.02
19.42
20.86
8.93
13.07
13.50
18.48
8.37
12.61
13.05
18.13
8.54
12.50

Thanks and cheers,
Rene
0

Author Commented:
Hi hgholt,

Thanks, you nailed it!  That's exactly what I need!!

Cheers,
Rene
0

IT ConsultantCommented:
OK, I see, but can you explain me what time represents 8.70 or 20.86?
0

Commented:
8.70 is 8 hours and 0.70*60 = 42 minutes.
0

Author Commented:
Hi Helpfinder,

.7 is the decimal value of the minutes.

Maybe I should have not mention the word time in my question.  That might have been confusing.  Sorry about that :(

8.70

60*.70=42

8h42

Cheers
0

Author Commented:
Hi Helpfinder,

I'll give you some points for your efforts and I may actually need to use your formula soon :)

Cheers,
Rene
0

Author Commented:
I am very grateful!
Thanks again :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.