# Thread: Time entries in 15 minute increments (Excel 2000)

1. ## Time entries in 15 minute increments (Excel 2000)

Does anyone know how to have Excel calculate time worked (beginning time+ending time) so that the sum is rounded up or down to the nearest 15-minute increment? Thanks muchly..........

2. ## Re: Time entries in 15 minute increments (Excel 2000)

Make sure that the Analysis ToolPak add-in has been installed/activated (Tools | Add-Ins...)
Say that the beginning time is in A1 and the ending time in B1. Use the following formula to calculate working time rounded to the nearest multiple of 15 minutes:

=MROUND(B1-A1,1/96)

Excel stores times as a fraction of 1 day. 15 minutes = 1/4 hour = 1/4 * 1/24 day = 1/96 day. The MROUND function rounds the first argument (working time) to the nearest multiple of the second argument (1/96 day = 15 minutes)

3. ## Re: Time entries in 15 minute increments (Excel 2000)

Cyndie

You can also use =CEILING((B1-A1)/0.04167,0.25) where 0.04167= 1/24 and where value B1> Value of A1

4. ## Re: Time entries in 15 minute increments (Excel 2000)

Thanks muchly, Jerry and Hans. I guess I don't have the Analysis ToolPak add-in because the "mround" thingee didn't work, but the "ceiling" thingee did---which keeps the boss happy. Thanks again...........

5. ## Re: Time entries in 15 minute increments (Excel 2000)

The solution with CEILING will suit your purpose, but isn't Analysis ToolPak listed when you select Tools | Add-Ins...?

6. ## Re: Time entries in 15 minute increments (Excel 2000)

Ahhh, yes, okay.... it's there. And when I check it and then insert the "=mround" equation you sent me, the equation works (that is, I do NOT get an error message) but by the same token, I do NOT get the right answer. I DO with the "=ceiling" equation. Since all I need is the right answer, that works for me. But thanks for pursuing it just a little further; I appreciate your taking the time.

7. ## Re: Time entries in 15 minute increments (Excel 2000)

I hope you are aware that Jezza's formula will always round UP, not up or down as you asked.

8. ## Re: Time entries in 15 minute increments (Excel 2000)

Well, since you brought it up, let me show you the problem. Using your equation,
=MROUND((+C1-B1-D1),1/96),
where C1 is the time out, B1 is the time in, and D1 is an hour for lunch,
I get the following result: 0.34.

Excel wants to modify your equation as follows:
=SUM(MROUND((+C1-B1-D1),1/96)), which looks a little different from yours but gives the same result: 0.34. Which is the wrong answer!

So, what now, coach?

9. ## Re: Time entries in 15 minute increments (Excel 2000)

Select the cell or cells with the formula, then select Format | Cells and apply a Time format.

10. ## Re: Time entries in 15 minute increments (Excel 2000)

Hey, it works!!

Boy, Hans, whatever it is that you do, I hope they're payin' you enough!! Thanks SO much.

11. ## Re: Time entries in 15 minute increments (Excel 2000)

Oh, the Lounge pay is a nice round figure! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

12. ## Re: Time entries in 15 minute increments (Excel 2000)

And they double yours every month! <img src=/S/yep.gif border=0 alt=yep width=15 height=15>

13. ## Re: Time entries in 15 minute increments (Excel 2000)

Cyndie

I wish I worked for your company, if you had used my formula I would be leaving at 1701hrs every day, I would get 4 hours and 40 mins extra pay a month unless you want to round that up too <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

14. ## Re: Time entries in 15 minute increments (Excel 2000)

A day late and a dollar short (Hans already earned it <img src=/S/yep.gif border=0 alt=yep width=15 height=15>), but see if this works without the ToolPack:

<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>1</td><td align=center>Start</td><td align=center>End</td><td align=center>Diff</td><td align=center>2</td><td align=right>12:01:00 PM</td><td align=right>12:31:00 PM</td><td>=ROUND((B2-A2)*96,0)/96</td></table>
Format Cell C2 as Hans explained (or for periods > 24 hours, with the custom format [h]:mm).

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•