[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

Rounding time based on a minite given.

Posted on 2011-10-03
Medium Priority
221 Views
basically I need to convert times into the nearest 30min interval and based a mintue given by the user it allows the person to be late by that minute or leave early by that minute.
I will try to explain below:

IN RND is rounding back to the nearest 30min interval
if after that given min then it will round to the next
30min interval.
OUT RND is forward to the nearest 30min interval if within
a given min time before that 30 min interval.
if before that given min time then it will round back
to the earlier 30 min interval.
I have an example below for rounding given the
IN RND & OUT ROUND is 5mins :
The next example is of the data and in RED is what
I want a button to calculate.
The IN and OUT is for each ID for each day.
0
Question by:RobJanine
• 5
• 4

LVL 6

Expert Comment

ID: 36907914
I'm a bit confused by your question, but if you want to round by time, say the nearest 15 minute interval per hour, the formula is below:
=ROUND(SUM(A2:A99)*96,0)/96

There are 96 quarter-hours in a day. Multiplying the total time by 96, rounding, and then dividing by 96 will give you the nearest quarter hour. If you want 30 min intervals, it would be 24, and so on, etc.

Do you have a sample file?
0

Author Comment

ID: 36907920
example 1:
20/08/2011 5:03            5.00
20/08/2011 5:04            5.00
20/08/2011 5:05            5.30
20/08/2011 5:06            5.30
20/08/2011 5:07            5.30
20/08/2011 5:34            5.30
20/08/2011 5:35            6.00
20/08/2011 5:36            6.00
20/08/2011 6:03            6.00
20/08/2011 6:04            6.00
20/08/2011 6:05            6.30
20/08/2011 6:06            6.30
20/08/2011 6:33            6.30
20/08/2011 6:34            6.30
20/08/2011 6:35            7.00
20/08/2011 6:34            7.00
20/08/2011 7:03            7.00
20/08/2011 7:04            7.00
20/08/2011 7:05            7.30

example 2:
A      B            C      D      E
1                              IN RND      OUT RND
2                              5      5
3      ID      DATE / TIME
4      5      18/08/2011 4:58            5.00      AM
5      5      18/08/2011 14:53            2.30      PM
6      6      18/08/2011 5:03            5.00      AM
7      6      18/08/2011 15:10            3.00      PM
8      8      18/08/2011 5:08            5.30      AM
9      8      18/08/2011 15:20            3.00      PM
10      10      18/08/2011 5:32            5.30      AM
11      10      18/08/2011 14:27            2.30      PM
12      11      18/08/2011 5:28            5.30      AM
13      14      18/08/2011 5:35            6.00      AM
14      14      18/08/2011 15:45            3.30      PM
0

LVL 6

Expert Comment

ID: 36907929
OK, try this formula in cell A1

=ROUND(A1*288,0)/288

0

LVL 6

Expert Comment

ID: 36907933
whoops, try this for every 15 min:
=ROUND(A1*48,0)/48

the prior one is every 5 min
0

Author Comment

ID: 36907939
Example file below
Example-Rounding.xlsx
0

Author Comment

ID: 36907970
ok....that works using 24 to round to the nearest 30 min interval.....now what about the IN and OUT round values. I need to change the time by that figure of 5mins first then round. How do I add 5min to a time?
0

LVL 6

Accepted Solution

Metallimirk earned 2000 total points
ID: 36907992
TO add 5 min, use the TIME format. formula is below:

=A1+TIME(0,5,0)
0

Author Comment

ID: 36908038
thankyou...all works fine....used the following formula:
=ROUND((B4+TIME(0,(15-C2),0))*48,0)/48
0

LVL 6

Expert Comment

ID: 36908044
Great.
0

Featured Post

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Suggested Courses
Course of the Month19 days, 18 hours left to enroll