Rounding military time to nearest tenth of an hour

Hi rcterrell,

Originally Posted by rcterrell

I'll try to explain this one as clear as possible. Here's the setup:

A1 - 08:00
B1 - 12:24
C1 - 13:15
D1 - 17:01
E1 - (Total Hours)

Okay. As you can see E1 is adding my employee's time in, lunch out, etc.

E1 has a formula already: =(B1<A1)+B1-A1+(D1<C1)+D1-C1

and the E1 cell uses [H]:mm custom format.

My problem is this: I need to have E1 round to the nearest tenth of an hour, but haven't been able to figure out the formula or how to nest the formula in addition to what I already have in E1.

Any help will be greatly appreciated!

Try this:

=ROUND(E1/((1/24)/10),0)*(1/24)/10

E1 = 8h, 10 mins

This will return 8h 12 mins (8.2 hours)


HTH,

Alan.

Thanks again Alan. I wasn't able to nest it, so I just created another column for the rounded figures.

Hircterrell,

Originally Posted by rcterrell

Thanks again Alan. I wasn't able to nest it, so I just created another column for the rounded figures.

Not sure what you mean, but perhaps this is what you need:

=ROUND(((B1<A1)+B1-A1+(D1<C1)+D1-C1)/((1/24)/10),0)*(1/24)/10

HTH,

Alan.

Perfect! Thanks again Alan!

Hi rcterrell,

Originally Posted by rcterrell

Perfect! Thanks again Alan!

You're welcome!

Alan.