Solved

Rounding time

Posted on 2014-04-04
9
374 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
 
LVL 10

Author Comment

by:ReneGe
ID: 39978200
Hi hgholt,

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

Cheers,
Rene
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Article by: Nadia
Linear search (searching each index in an array one by one) works almost everywhere but it is not optimal in many cases. Let's assume, we have a book which has 42949672960 pages. We also have a table of contents. Now we want to read the content on p…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

707 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now