Round up to whole hours

My formula calculates the time 2 hours from now and I need it to round up to
the next hour:

=NOW()+TIME(2,0,0)

I tried a few things but they didn't work!

On Wed, 28 Jun 2006 18:10:01 -0700, ivory_kitten
<[email protected]> wrote:

>My formula calculates the time 2 hours from now and I need it to round up to
>the next hour:
>
>=NOW()+TIME(2,0,0)
>
>I tried a few things but they didn't work!

The CEILING function should do what you want.

Try this:

=CEILING(NOW()+TIME(2,,),TIME(1,,))


--ron

And if one doesn't want to include all days since 1900 one could use

=CEILING(MOD(NOW(),1)+TIME(2,,),TIME(1,,))

or even

=CEILING(MOD(NOW(),1)+2/24,1/24)

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Wed, 28 Jun 2006 18:10:01 -0700, ivory_kitten
> <[email protected]> wrote:
>
>>My formula calculates the time 2 hours from now and I need it to round up
>>to
>>the next hour:
>>
>>=NOW()+TIME(2,0,0)
>>
>>I tried a few things but they didn't work!
>
> The CEILING function should do what you want.
>
> Try this:
>
> =CEILING(NOW()+TIME(2,,),TIME(1,,))
>
>
> --ron

Problem solved, didn't seem to need to use the mod bit, but will keep in mind
just in case!

Thanks guys!

"Peo Sjoblom" wrote:

> And if one doesn't want to include all days since 1900 one could use
>
> =CEILING(MOD(NOW(),1)+TIME(2,,),TIME(1,,))
>
> or even
>
> =CEILING(MOD(NOW(),1)+2/24,1/24)
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> Excel 95 - Excel 2007
> Northwest Excel Solutions
> www.nwexcelsolutions.com
> "It is a good thing to follow the first law of holes;
> if you are in one stop digging." Lord Healey
>
>
> "Ron Rosenfeld" <[email protected]> wrote in message
> news:[email protected]...
> > On Wed, 28 Jun 2006 18:10:01 -0700, ivory_kitten
> > <[email protected]> wrote:
> >
> >>My formula calculates the time 2 hours from now and I need it to round up
> >>to
> >>the next hour:
> >>
> >>=NOW()+TIME(2,0,0)
> >>
> >>I tried a few things but they didn't work!
> >
> > The CEILING function should do what you want.
> >
> > Try this:
> >
> > =CEILING(NOW()+TIME(2,,),TIME(1,,))
> >
> >
> > --ron
>
>
>