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 ??

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 How do I post code blocks?

=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.)

Hmmm... I'll have a go at that tomorrow. Thanks for the quick reply.

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.)

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.

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.

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

Ahhhhh yes. Makes sense now. I don't often use Excel; hence the question.

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!

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.

Ditto. I learned a bit from it too. So you guys can see what I was working on and maybe improve on it, I'll upload the finished product and a sample to show how it works.

---

Attachments

time-template.zip (11.1 KB, 7 views)

BTW, thanks again for the help fellas.