I am not too understanding of excel to any great extent. But I need to round
time to the nearest quarter.
Example
8:11 to 8.25
4:41 to 4.75
I am not too understanding of excel to any great extent. But I need to round
time to the nearest quarter.
Example
8:11 to 8.25
4:41 to 4.75
I am assuming from you example that you are rounding up the time.
Here are the two solutions:
=CEILING(A1, TIME(0,15,0))*24
=TIME(HOUR(A1),CEILING(MINUTE(A1),15),0)*24
Google is your best friend!
If you want the answer in decimal hours use this formula and format the cell
as a number
=ROUND(A1*24*4,0)/4
If you want the answer in Excel time use this formula and format the cell as
time
=ROUND(A1*24*4,0)/24/4
These round to the nearest quarter hour so 4:17 will yield 4.25 or 4:15
"Meghan" wrote:
> I am not too understanding of excel to any great extent. But I need to round
> time to the nearest quarter.
>
> Example
>
> 8:11 to 8.25
> 4:41 to 4.75
Thanks alot these should help
"Bearacade" wrote:
>
> I am assuming from you example that you are rounding up the time.
>
> Here are the two solutions:
>
> =CEILING(A1, TIME(0,15,0))*24
>
> =TIME(HOUR(A1),CEILING(MINUTE(A1),15),0)*24
>
>
> --
> Bearacade
>
>
> ------------------------------------------------------------------------
> Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
> View this thread: http://www.excelforum.com/showthread...hreadid=558939
>
>
Thanks alot these should really help
"Sloth" wrote:
> If you want the answer in decimal hours use this formula and format the cell
> as a number
> =ROUND(A1*24*4,0)/4
>
> If you want the answer in Excel time use this formula and format the cell as
> time
> =ROUND(A1*24*4,0)/24/4
>
> These round to the nearest quarter hour so 4:17 will yield 4.25 or 4:15
>
> "Meghan" wrote:
>
> > I am not too understanding of excel to any great extent. But I need to round
> > time to the nearest quarter.
> >
> > Example
> >
> > 8:11 to 8.25
> > 4:41 to 4.75
On Thu, 06 Jul 2006 16:39:53 +0100, Bearacade =
<[email protected]> wrote:
>
> I am assuming from you example that you are rounding up the time.
>
> Here are the two solutions:
>
> =3DCEILING(A1, TIME(0,15,0))*24
>
> =3DTIME(HOUR(A1),CEILING(MINUTE(A1),15),0)*24
>
>
Not used Ceiling() before, why won't it accept negative numbers.
It is meant to round up to - no mention of only working with >=3D0 numbe=
rs
-- =
Steve (3)
Thank you
"SteveW" wrote:
> On Thu, 06 Jul 2006 16:39:53 +0100, Bearacade
> <[email protected]> wrote:
>
> >
> > I am assuming from you example that you are rounding up the time.
> >
> > Here are the two solutions:
> >
> > =CEILING(A1, TIME(0,15,0))*24
> >
> > =TIME(HOUR(A1),CEILING(MINUTE(A1),15),0)*24
> >
> >
>
> Not used Ceiling() before, why won't it accept negative numbers.
>
> It is meant to round up to - no mention of only working with >=0 numbers
>
> --
> Steve (3)
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks