Roundup ?

Topic

New Reply

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.

Thanks in advance

Braddy

Reply | Quote

Replies

Hi Braddy

Is this hrs:mins or mins:secs??

zeddy

Reply | Quote

Hi Zeddy

It’s hrs:mins

Thanks for the reply
Braddy

Reply | Quote

Hi Braddy

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.
We first convert your answer in days to give an answer in decimal hours:
(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

Reply | Quote

Hi Zeddy

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

Regards
Braddy

Reply | Quote

Change the 24s to 48s

Steve

Reply | Quote

Hi Steve

That’s great

Thanks to you and Zeddy, I am in your debt

Regards

Braddy

Reply | Quote

As an alternative, I think

IF(H4=””,””,CEILING(H4-G4,TIME(0,30,0))
would do it.

Reply | Quote

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?

Reply | Quote

Hi Rory

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

Thanks

Braddy

Reply | Quote

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

Steve

Reply | Quote

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!

Reply | Quote