# Thread: Time in increments (Excel 2000)

1. ## Time in increments (Excel 2000)

(Edited by HansV to provide link to post - see <!help=19>Help 19<!/help>)

I was using the formula Hans gave in post re: <post#=517237>post 517237</post#> from CBrowning, but it is not working on my sheet. I am using 30 minute increments, and not 15...that may be the problem. could someone take a peek at the timesheet I have and tell me where I am going wrong. Is it my cell format? Is it the formula?
Thank you

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

I assume your problem concerns cell I3 on the Instructor & Class Info sheet.
1) If you want to round to 30 minutes, use

=MROUND(H3-G3,1/48)

The reason for 1/48 is that 30 minutes = 1/2 hour = 1/48 day. Excel stores dates and times with days as unit.

2) You must use Format | Cells... to format I3 as a time.

To get the result in J3 right, you must multiply the value in I3 or in J3 with 24, since the above formula uses 1 day as unit, and the pay is by the hour.
For example, change the formula in J3 to

=IF(ISERROR(I3*F3),"",I3*F3*24)

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

Two things:

1- If you read HANS' post, you will find that 1/96 = 1/4hour * 1/24 day. If you want to use 30 minutes, that would be 1/2 hour * 1/24 day = 1/48. Therefore, you need to use 1/48 in the MROUND function.

2- Cell I3 is not formatted in a Time format. Therefore, it is displaying the fractional part of a day, not a time.

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

Hans,
I did make that change after posting the message. But in the final Pay cell it only gives me \$.94.

Instructor HrPa StartTime EndTime Total Hrs Total Pay

Christine Ly \$15.00 07:30 09:00 1:30 \$0.94

And I3 is formatted as time...eg. 13:30 and still only get .94 cents

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

That's because you did not convert Excel's time value (fractional part of a day) into decimal hours. The formula should be:

=IF(ISERROR(I3*24*F3),"",I3*24*F3)

6. ## Re: Time in increments (Excel 2000)SOLVED

Thank you Hans...That was it. And you to Legare...both are wonderful

=IF(ISERROR(I3*F3),"",I3*F3*24)

#### Posting Permissions

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