# Rounding military time to nearest tenth of an hour

1. ## Rounding military time to nearest tenth of an hour

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!

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.

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

5. Perfect! Thanks again Alan!

Hi rcterrell,

Originally Posted by rcterrell
Perfect! Thanks again Alan!
You're welcome!

Alan.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1