Rounding Times Down to a Specified Time Increment in Microsoft Excel 2010

 

In this article, we will learn how toround the times down to a specified time increment.We will use a combination of“TIME”, “HOUR”, “MINUTE” and “FLOOR” functionsin Microsoft Excel 2010.

FLOOR: – This function is used to round a number down to the nearest multiple of significance.

Syntax of “FLOOR” function:=FLOOR (number, significance)

Example:Cell A2 contains the number 456.25

=FLOOR (A2, 0.75), function will return 456
 
img1
 
HOUR: This function is used to return the hour as a number from 0 (12:00 AM) to 23 (11:00 PM).

Syntax of “HOUR” function:          =HOUR(serial_number)

For Example:Cell A1 contains 15:00 (3:00 PM).

  • Write the formula in cell B1.
  • =HOUR(A1), andpress Enter on your keyboard.

 
img2
 
MINUTE: This function is used to return the minute a number from 0 to 59.

Syntax of “MINUTE” function:      =MINUTE(serial_number)

For Example:Cell A1 contains 15:10 (3:10 PM).

  • Write the formula in cell B1.
  • =MINUTE(A1), and press Enter on your keyboard.

 
img3
 
TIME: -It converts hours, minutes, and seconds given as numbers to an Excel serial number, formatted with a time format.

Syntax: =TIME(hour,minute,second)

hour: A number from 0 to 23 representing hour

minute: A number from 0 to 59 representing minute

second: A number from 0 to 59 representing second

 

Let us take an example:

  • We have hours listed in column A & minutes in column B
  • We want a function that will combine both of them & return Time.
  • The function in cell C2 would be =TIME(A2,B2,0)

 
img4
 
Let’s take an example to understand how we can round times down to a specified time increment.

We have a time list formatted as “hh:mm:ss” in column A. Now, we want to round down to a specified time increment.Then we need to follow below given steps:-
 
img5
 

  • Write the formula in cell B2.
  • =TIME(HOUR(A2),FLOOR(MINUTE(A2),15),0)
  • The function will round the times down to a specified time increment.

 
img6
 

  • Copy the same formula by pressing the keyCtrl+C and paste in the range B3:B6 by pressing the key Ctrl+V on your keyboard.

 
img7
 
This is the way we can round the times down to a specified time increment in Microsoft Excel.
 
 



Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>