Solved

Rounding time

Posted on 2014-04-04
9
379 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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.

773 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