# rounding time values in excel

• May 09th, 2004, 12:17 AM
skrlin
What I'm looking to do is create a spreadsheet that will calculate a paycheck based on the times inputed on the days worked. Here's the catch: I want excel to round the minutes up or down to 15 min increments. It would round up 7 minutes and 30 seconds after each increment but I don't want to be that technical, instead it would round at 7 whole minutes
Explanation:
7:01 - 7:07 would get rounded down to 7:00
7:08 - 7:14 would get rounded up to 7:15
7:16 - 7:22 would get rounded down to 7:15
7:23 - 7:29 would get rounded up to 7:30
...and so on.

I can't figure out how to use the functions to round minutes out of the time format without splitting the time into hours and minutes in different cells, which I don't want to do. Any ideas ?

I'm open to suggestions for other ways of doing this in PHP/MySQL, JavaScript (however sloppily). Any ideas ??
• May 09th, 2004, 12:35 AM
TheGAME1264
From Excel directly, you could enter in a series of nested if statements. It's messy as all hell, but it will work.

Something like this:
Code:

`=IF(MINUTE(A1)>7,IF(MINUTE(A1)>22,IF(MINUTE(A1)>37,IF(MINUTE(A1)>52,0,45),30),15),0)`
That will create your minute value based on your interval. I'll leave it to you to tweak the rest to come up with the appropriate hour value (since there is a case after 52 where it differs, and I'm too tired to think of it.)
• May 09th, 2004, 12:37 AM
skrlin
Hmmm... I'll have a go at that tomorrow. Thanks for the quick reply.
• May 09th, 2004, 12:52 AM
TheGAME1264
You got it, buddy. Let me know how that works out.

(By the way, that formula is correct. I checked it in Excel and C&Ped it.)
• May 09th, 2004, 02:59 PM
benbun
Ok assuming your time (to be rounded is in A1), you could use the following.

=IF(MOD(A1,(1/96))<(1/192),A1-MOD(A1,(1/96)),A1-MOD(A1,(1/96))+(1/96))

if you want I can forward an example spreadsheet showing this in action.
• May 09th, 2004, 04:02 PM
TheGAME1264
benbun: could you upload it please? I'd like to see the example and understand the formula myself. I'm assuming MOD is the same as it is in VBScript (a remainder), but why you have numbers in fractions is semi-confusing to me.
• May 10th, 2004, 05:40 AM
benbun
I will upload the file once I get home as it is on my home PC.

To understand the fractions you need to understand how Excel deals with times.

Excel splits the day into seconds.
Therefore one day has 24 * 60 * 60 = 86,400 seconds ( Hours x minutes x seconds ).

Therefore 1/86400 = 1 second.

Using this we can say 15 minutes = 15 x 60 = 900 seconds. To denote this in Excel we use 900 x 1/86400 = 900/86400.

This simplifies to 1/96 and hence the mysterious fraction used in the formula.

Hope this helps :)
• May 10th, 2004, 08:48 AM
TheGAME1264
Ahhhhh yes. Makes sense now. I don't often use Excel; hence the question.
• May 10th, 2004, 09:51 PM
skrlin
Well, I tried your formula a couple of different ways Adam, but to avail...but when I tried benbun's I just changed the cell number and it worked like a charm. And he even helped me to figure out my next question (about calculating overtime) with his next post. So I awarded benbun the TD. I appreciate the effort though. Thanks guys!
• May 10th, 2004, 10:30 PM
TheGAME1264
No harm, bro. I liked his way better than mine anyway. To get mine to work would have required some extra effort and been way more complex a formula anyway. Besides, I learned something.

benbun earned them, as far as I'm concerned.