Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Rounding time

Posted on 2014-04-04
9
380 Views
Last Modified: 2014-04-04
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
Comment
Question by:ReneGe
  • 5
  • 2
  • 2
9 Comments
 
LVL 19

Assisted Solution

by:helpfinder
helpfinder earned 100 total points
ID: 39978166
use this formula
=(ROUND((A1*1440)/15,0)*15)/1440

see also my sample if it works as you desire
sample.xlsx
0
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 400 total points
ID: 39978177
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
 
LVL 10

Author Comment

by:ReneGe
ID: 39978192
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 10

Author Comment

by:ReneGe
ID: 39978200
Hi hgholt,

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

Cheers,
Rene
0
 
LVL 19

Expert Comment

by:helpfinder
ID: 39978202
OK, I see, but can you explain me what time represents 8.70 or 20.86?
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 39978219
8.70 is 8 hours and 0.70*60 = 42 minutes.
0
 
LVL 10

Author Comment

by:ReneGe
ID: 39978224
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
 
LVL 10

Author Comment

by:ReneGe
ID: 39978230
Hi Helpfinder,

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

Cheers,
Rene
0
 
LVL 10

Author Comment

by:ReneGe
ID: 39978238
I am very grateful!
Thanks again :)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Prime numbers are natural numbers greater than 1 that have only two divisors (the number itself and 1). By “divisible” we mean dividend % divisor = 0 (% indicates MODULAR. It gives the reminder of a division operation). We’ll follow multiple approac…
Article by: Nicole
This is a research brief on the potential colonization of humans on Mars.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question