1. ## Roundup ?

Hi

I use this formula to calculate time IF(H4="","",H4-G4) the result in this instance is 1:59 I want to roundup to 2:00 I won't bore you with the reason why unless it becomes necessary.

Is this hrs:mins or mins:secs??

zeddy

3. Hi Zeddy

It's hrs:mins

So, you want to round up to the nearest hour?

In Excel, all time is stored in cells in decimal units of 'days', irrespective of your chosen display format.
So your calc (H4-G4) gives a result in 'days' but displays in your chosen format.
(H4-G4)*24 e.g. 1.965432
Then use
=ROUNDUP((H4-G4)*24,0) to round up to the nearest hour.
Then convert back into days for the cell value:
=ROUNDUP((H4-G4)*24,0)/24

zeddy

5. Hi Zeddy

Thanks very much for that, however I should have made it clear I need to roundup to the nearest half hour.

Regards

6. Change the 24s to 48s

Steve

7. Hi Steve

That's great

Thanks to you and Zeddy, I am in your debt

Regards

8. As an alternative, I think
IF(H4="","",CEILING(H4-G4,TIME(0,30,0))
would do it.

9. Hi Rory

Never heard of the ceiling function before, I will give it a try

Thanks

10. Originally Posted by rory
As an alternative, I think
IF(H4="","",CEILING(H4-G4,TIME(0,30,0))
would do it.
Fascinating. We have a timesheet that calculates the hours worked with =sum(I6-D6)-(F6-E6)-(H6-G6) and puts the results in the J6 cell. D6 being the time they first arrive, I6 being the time they leave for the day, and the other four are for two in and out times during the day for lunch or appointments.

The trouble we've had was rounding up the real time to the quarter hour. At the end of the day J6 might equal 7:54. We've left it up to the user to translate that into a useful number rounded to the nearest quarter. This number rounds up to 8. If it had been 7:52, the user rounds down to 7.75.

Your use of CEILING and TIME is the closest I've been to solving how to have the timesheet do that rounding instead of the user. Is there a way to do that, rounding to nearly 15 minute increment?

11. Something like:
=CEILING(j6,TIME(0,15,0))

Steve

12. Here's the formula we came up with:

=ROUND((SUM(H12-C12)-(E12-D12)-(G12-F12))*96,0)/96

C12 is when the person arrives, H12 is when they leave at the end of the day, and the others are for two breaks like for lunch or an appointment. The formula rounds to the nearly 15 minutes and then converts the time into a usable number for our bookkeeper.

Without the help I get here we'd still be doing this the hard way. Thank you!

#### Posting Permissions

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