Rounding up Time Values

A

ADE2

Hi

I am using the formula below to round time values up,in this case t
the next five minute increment


=(TIME(HOUR(BB14),CEILING(MINUTE(BB14),BB13),0))


BB13 = 5
BB14 = 16:00:02

This formula only rounds up to 16:05:02 if the time is 16:01:00 o
greater,i would prefer it if the formula could make the time round u
as soon as one second has passed, 16:00:01 and not when one minute ha
passed.

Can this be done

Ad
 
P

Peo Sjoblom

What is in BB14?
You can just use

=CEILING(BB14,5/1440)

will do what you want

Or if there are dates as well you can just format as time or remove the date

=CEILING(MOD(BB14,1),5/1440)

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
B

Bob Phillips

Ade,

Is this what you want,

=ROUNDUP(BB14*24*(60/BB13),0)/24/(60/BB13)

or

=CEILING(BB14*24*(60/BB13),1)/24/(60/BB13)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
A

ADE2

I have used the following formula to round up the time in cell BB3 t
the nearest five minutes.

=CEILING(BB3,5/1440)

this rounds up 11:20:01 to 11:25:00

I now need to round up to the nearest four hour increments

round up at desired value
00:00:01 04:00:00
04:00:01 08:00:00
08:00:01 12:00:00
12:00:01 16:00:00
16:00:01 20:00:00
20:00:01 00:00:00

So i only want it to round up 6 times per day at the times shown in th
left column(at one second past the four hour period)

Not sure how to approach this


Thanks for the help


Ad
 
P

Peo Sjoblom

One way

=CEILING(BB3,4/24)

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
B

Bob Phillips

My formula was based upon your original post and assumed the rounding
minutes factor in BB13. So you could have put 240 in there and it would have
worked.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top