Convert Time to Decimal in Excel

 

In this excel tricks, excel convert times to decimal values, for which we will use Hour and Minute formula in Microsoft Excel.

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), press Enter on your keyboard.

img1

MINUTE: This function is used to return the minute as 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), press Enter on your keyboard.

img2

Let’s take an example to understand how to convert  time to decimal Excel.

Column A contains the time as “3:30 PM” and we want to convert it in Decimal so that it will display 30 minute as half of hour or display the 30 minutes as a half hour or display the result as “15:50”
img3

Follow below given steps:-

  • Write the formula in cell B2.
  • =HOUR(A2)+(MINUTE(A2)/60)
  • Press Enter on your keyboard.
  • The function will return the time to decimal values.

img4

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

img5

If you want to convert minute value, you can use formula called “CONVERT”.

Follow below given steps:-

  • Write the formula in cell C2.
  • =HOUR(A2)+CONVERT(MINUTE(A2),”mn”,”hr”)
  • Press Enter on your keyboard.
  • The function will return the same result as above mentioned formula returned.

img6

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

img7

This is the way we can convert the times to decimal in Microsoft Excel.

How to Excel convert decimal to time value

  • Write the formula in cell D1.
  • =B2/24, press Enter on your keyboard.
  • The function will convert the decimal values into correctly formatted time values.

img8

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

img9

This the way we can convert times to decimal values 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>