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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Thanks alot these should really help

Sloth said:
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
Click to expand...
 
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
Click to expand...

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

SteveW said:
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
Click to expand...
 
Back
Top