Rounding up/down of time values

Hello,

I need a quick formula to re-calculate a time format to that of 30sec blocks

eg

Data entered in;
A = 1.29 returns 1.30 in B
A = 1.56 returns 2.00 in B
A = 21.19 returns 21.30 in B
A = 1.51 returns 2.00 in B
A =0.16 returns 0.30 in B

etc, etc, etc.

Hoping this is achievable, thanks in advance,

Mike

Hi Mike,
and what sould 1.30 return? 1 or 2 ?

Originally Posted by arthurbr

Hi Mike,
and what sould 1.30 return? 1 or 2 ?

Ahaha,..

I'll have to use the dollarde formula as well to revert times such as 1.5Mins into real time such as 1:30 ???

Thanks

My assumption would be that 1.30 rounds to 1.30...but it's not clear from your examples whether you are rounding up or just rounding (to the nearest). What should 1.04 round to?

If you're always rounding up then that should become 1.30 and you could use this formula in B1

=TEXT(CEILING(TEXT(A1*100,"0\:00")+0,"0:30"),"h.mm")+0

or for rounding to 1

=INT(A1)+LOOKUP(MOD(A1,1),{0,0.15,0.45;0,0.3,1})

Note: it would be simpler if your "times" were in true time format, i.e. with a colon, then you can roundup with a formula like

=CEILING(A1,"0:30")

or round with

=ROUND(A1*48,0)/48

[QUOTE=daddylonglegs]My assumption would be that 1.30 rounds to 1.30...but it's not clear from your examples whether you are rounding up or just rounding (to the nearest). What should 1.04 round to?

If you're always rounding up then that should become 1.30 and you could use this formula in B1

=TEXT(CEILING(TEXT(A1*100,"0\:00")+0,"0:30"),"h.mm")+0

I think this will be the one. I just need to incorporate the dollarde formula as times are entered in true format, such as 1:30 secs, which is calculated as 1.5.


Appreciate your assistance..

Mike

Ok..

Hopefully the attached will make my quest a little clearer.

Column B needs to roundUP to the nearest 30sec block
Column C needs to convert the .3's into .5's so that it calculates 3.3 as if it was 3 and a half mins.

Sorry If I have not made this very clear.

B3 could be the earlier formula I suggested, i.e.

=TEXT(CEILING(TEXT(A3*100,"0\:00")+0,"0:30"),"h.mm ")+0

and then in C3

=CEILING(B3,0.5)

Originally Posted by daddylonglegs

B3 could be the earlier formula I suggested, i.e.

=TEXT(CEILING(TEXT(A3*100,"0\:00")+0,"0:30"),"h.mm ")+0

and then in C3

=CEILING(B3,0.5)

That fixed it....

Thank you kindly..

Mike