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.

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.

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”

• 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.

• 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.

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

• 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.

• 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.

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.

• 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.

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

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.