TRICKS TO CONVERT TIME INTO DECIMAL IN GOOGLE SHEETS

Table of Contents

Time is one of the challenging formats to handle in any spreadsheet application say Excel or Google Sheets. It is because it works separately in the background whereas the front end is only a format.

For example, the time which is presented in the format hh:mm or hh:mm:ss or any other format is simply a decimal number in real.

Whenever we perform any type of mathematics on time say adding the hours or subtracting the hours etc., the calculation will be done on the decimals and the result will again be changed into the previous format.

In this article, we’d learn the way to convert the given time into decimal.



HOW TIME IS REPRESENTED AS DECIMAL IN GOOGLE SHEETS?

Time is simply represented as the decimal portion in Google Sheets. It means 00:00 am is represented as 0.0 whereas 12:00 pm is 0.5 and 12:00 am will be 1.00 but midnight of the next day.

It simply means that 24 hours are divided as a part of 1.

24 hours =1.00

Simply divided 1.00 by the required division and you’ll get the result.

We’ll learn the ways to convert decimal number to time and time to decimal number in the given examples.



STEPS TO CONVERT GIVEN TIME TO DECIMAL NUMBER IN GOOGLE SHEETS

It is very easy to convert the given time into decimal.

Follow the steps to convert time to decimal number in Google Sheets:

  1. Simply select the cell or cells where you want to convert the given time into decimal.
  2. Go to MORE FORMATS and choose NUMBER in the STANDARD TOOLBAR which is visible in Google Sheets as shown in the picture below.
  3. You are done.
  4. The cell value which was in TIME FORMAT will be replaced by its equivalent decimal number.

You can also go to FORMAT MENU and choose NUMBER> NUMBER to get the same effect if you can't find the MORE FORMATS option on Toolbar.

The following animation shows the procedure.

CONVERT TIME TO DECIMAL

HOW TO CONVERT TIME TO DECIMAL USING MANUAL METHOD?

You can also make use of the manual method to perform the same task.

  • Open CALCULATOR in windows or use manual calculator.
  • Find out the total hours, minutes and seconds counting from the 12 AM midnight.
  •  For example is 10 AM which means 10 hours 00 minutes and 00 seconds from the midnight.
  • Multiply the hours with the hour factor [derived above 0.041667], minutes with the minute factor [0.0006944] and seconds with the seconds factor [0.00001157].
  • The result comes out to be 10*0.041667 =0.41667.
  • This is the decimal equivalent of the given time.