# How do I round time to the nearest quarter of an hour

1. ## How do I round time to the nearest quarter of an hour

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

2. 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

3. ## RE: How do I round time to the nearest quarter of an hour

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

4. ## Re: How do I round time to the nearest quarter of an hour

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
>
>

5. ## RE: How do I round time to the nearest quarter of an hour

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

6. ## Re: How do I round time to the nearest quarter of an hour

On Thu, 06 Jul 2006 16:39:53 +0100, Bearacade =

<Bearacade.2aj5wo_1152200414.4187@excelforum-nospam.com> 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)

7. ## Re: How do I round time to the nearest quarter of an hour

Thank you

"SteveW" wrote:

> On Thu, 06 Jul 2006 16:39:53 +0100, Bearacade
> <Bearacade.2aj5wo_1152200414.4187@excelforum-nospam.com> 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)
>

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1