Round time to 15 min intervals in Excel

Round time down or up to nearest 15 minute interval - quick Microsoft Excel tutorial. If you need to round time up to nearest 15 min interval, use this function (presuming your time data is in cell A1):
=CEILING(A1;1/96)



Note: depending on your regional settings, you might have to use comma (,) as a separator instead of semicolon (;): CEILING(A1,1/96)

Explanation:

For Excel, one hour is 1/24. This means: 
  • 1 min = 1/24*60=1/1440
  • 15 min = 1/1440/15=1/96
  • 30 min = 1/1440/30 = 1/48
If you need to round time down to nearest 15 min interval, use this function (presuming your time data is in cell A1):
=FLOOR(A1;1/96)

If you need to round time to nearest 15 min interval, use this function (presuming your time data is in cell A1):
=MROUND(A1;1/96)

No comments:

Post a Comment