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

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

Thanks for your help!

Cheers,
Rene
0
ReneGe
Asked:
ReneGe
  • 5
  • 2
  • 2
2 Solutions
 
helpfinderIT ConsultantCommented:
use this formula
=(ROUND((A1*1440)/15,0)*15)/1440

see also my sample if it works as you desire
sample.xlsx
0
 
Ejgil HedegaardCommented:
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
 
ReneGeAuthor Commented:
Thanks helpfinder for your prompt response :)

Your formula works on your spreadsheet, But not with my numbers.

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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
ReneGeAuthor Commented:
Hi hgholt,

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

Cheers,
Rene
0
 
helpfinderIT ConsultantCommented:
OK, I see, but can you explain me what time represents 8.70 or 20.86?
0
 
Ejgil HedegaardCommented:
8.70 is 8 hours and 0.70*60 = 42 minutes.
0
 
ReneGeAuthor 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
 
ReneGeAuthor 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
 
ReneGeAuthor 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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now