Round time to 30 min intervals | Round time to any interval you want in Excel

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



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

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 30 min interval, use this function (presuming your time data is in cell A1):
=FLOOR(A1;1/48)

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

2 comments:

  1. AWS Training in Bangalore - Live Online & Classroom
    myTectra Amazon Web Services (AWS) certification training helps you to gain real time hands on experience on AWS. myTectra offers AWS training in Bangalore using classroom and AWS Online Training globally. AWS Training at myTectra delivered by the experienced professional who has atleast 4 years of relavent AWS experince and overall 8-15 years of IT experience. myTectra Offers AWS Training since 2013 and retained the positions of Top AWS Training Company in Bangalore and India.


    IOT Training in Bangalore - Live Online & Classroom
    IOT Training course observes iot as the platform for networking of different devices on the internet and their inter related communication. Reading data through the sensors and processing it with applications sitting in the cloud and thereafter passing the processed data to generate different kind of output is the motive of the complete curricula. Students are made to understand the type of input devices and communications among the devices in a wireless media.

    ReplyDelete