Rounding up time

Can anyone help me with a formula to round time up in time and also in decimals

I ideally need both the time & decimals formulas to round up in 30 mins / 0.50 increments for the first hour only and then hourly for anything beyond an hour, I could do with this as a stand alone formula and also to be nested into this time formula

=IF(OR($G10="",$K10=""),"",CEILING(MAX(0,H10-G10),"00:30"))

I also then need the time in decimals equivalent of both, can anyone assist on this as at the moment it is rounding up in 30min increments throughout? Many thanks

Sorry my reply did not post for some reason.

Hi Pepe

Unfortunately this doesn't work, it throws up an error. Also I need to keep the time formula separate to the time in decimals formula as they are for different cells

it appears to be where I have underlined

=IF(OR($G10="",$K10=""),"",IF(and(h10-G10>0,h10-g10<1/24),CEILING(H10-G10,"00:30"),if(h10-g10>1/24),CEILING(H10-G10,1/24),"")))

my existing formulas works in 30mins / 0.5 increments I just need to add in a formula to change to hourly / 1.0 increments after first hour, just wanted to know what needs to be nested in the existing formula I gave or what standalone formula I can use (if I create another column instead)

Hi Marvin. The Mround rounds up or down, for this I need ceiling function, but the issue I have is that I need, it to round up in 30 mins increments for first hour then hourly thereafter

Pepe I was creating a sheet for you, it wasnt that I didn't want to post one

Example sheet up with comments

See if the following formulas work for you:

=IF(OR($G10="",$K10=""),"",CEILING(MAX(0,K10-G10),((MAX(0,K10-G10)>1/24)+1)/48)) to be formatted as [h]:mm

=IF(OR($G10="",$K10=""),"",CEILING(MAX(0,K10-G10),((MAX(0,K10-G10)>1/24)+1)/48))*24 for decimal hours

Works perfectly Root thank you, see attached sheet, yellow is the new formula, I've adapted it to work for the orange cells, but cant get it to work for the green ones, which is a bit odd.

Is there anyone who can help with this last query regarding the last work sheet I uploaded

Please try

=CEILING(MAX(0,C4),((MAX(0,C4)>1/24)+1)/48)*24

That is, please keep reference to column C, not to column D.

Hi

I was looking for the formula for column F to use column D not C

I do have a formula which would calculate F using E but that again wasnt what I was after

many thanks kvsrinivasamurthy it works perfectly