Convert Time to Decimal Quantity in Excel (Hours, Minutes, Seconds)

By default, date and time are saved as numbers/decimals in Excel. However you usually see it in several codecs due to the way in which the cells have been formatted.

For instance, within the under instance, the worth in column A and B is identical, however these are displayed in another way due to the format.

https://trumpexcel.com/wp-content/uploads/2019/12/Same-value-but-different-formats-of-time-in-Excel.png” />

And one of many issues many individuals want in Excel is to convert the time to decimal (equivalent to hours or minutes or seconds).

For instance, as an alternative of getting the date and time as 01-01-2020 6:30 AM, it’s your decision the get:

Variety of hours as 6Number of minutes as 30

Fortunately Excel has some superior formulation, you’ll be able to simply convert time to decimal values.

Methods to Convert Time to Decimal Numbers in Excel

On this tutorial, I’ll present you some examples of changing time to a decimal utilizing formulation (i.e, changing time to hours, minutes, and seconds).

There are a number of methods to convert time to decimal in Excel:

Utilizing arithmetic operation is the simplest approach to convert time to decimal numbers. For instance:To transform time to hours, multiply the time worth with 24To transform time to minutes, multiply the time worth with 24*60To transform time to seconds, multiply the time worth with 24*60*60You may as well use Excel in-built formulation equivalent to CONVERT or HOUR/MINUTE/SECOND

Within the sections that comply with, I’ll present you examples of the way to convert Excel time to decimal values utilizing these strategies.

So let’s get began!

Convert Time to Hours in Excel

On this part, I cowl three other ways to transform time to hours in Excel.

Utilizing Easy Multiplication to Get the Hour Worth

Suppose you have got the time in a cell as 6:00 PM  (in cell A2) and also you wish to convert it into hours.

You may merely multiply it by 24 and it will provide you with what number of hours have elapsed until that point.

=A2*24

https://trumpexcel.com/wp-content/uploads/2019/12/Convert-Time-to-Hours-using-simple-multiplication.png” />

This works due to the truth that Excel shops dates and occasions as numbers. A full day (24 hours) is saved as 1 in Excel. Because of this every hour is saved as 1/24.

So when you see the worth 6:00 PM, within the backend, it’s saved as the worth zero.75 (indicating that 18 hours out of the 24 hours have already handed). So, once you multiply it with 24, it provides you the variety of hours which have handed.

In case you have got a worth equivalent to 6:32 PM, utilizing the above system will provide you with the outcome as 18.53 (the place the minutes/seconds half is proven in decimals and full/full hours as integers). On this instance, the numeric worth of 32 minutes in Excel could be zero.53 hours.

Word: In case you get the hour worth that has the decimals (equivalent to 18.53), make you certain you have got formatted it to indicate the decimals as effectively. In case you haven’t, you may even see the outcome as 19, as Excel exhibits you the rounded off integer worth. Nonetheless, this wouldn’t change the worth within the cell, which is able to proceed to be 18.53

In case you solely need the whole/full hour worth and ignore the minutes half, use the under system utilizing INT.

=INT(A2*24)

https://trumpexcel.com/wp-content/uploads/2019/12/Use-INT-formula-to-get-full-hour-value-only.png” />

INT solely provides you the integer half and also you don’t have to fret about any additional minutes.

Utilizing Excel Features (HOUR, MINUTE, and SECOND)

If you happen to don’t just like the guide multiplication technique, one other very easy approach to convert time to hours is by utilizing the inbuilt time-related  features (HOUR, MINUTE, and SECOND)

Suppose, you have got the occasions as proven under and also you wish to know what number of hours have elapsed within the day.

https://trumpexcel.com/wp-content/uploads/2019/12/Time-to-Convert-to-Hour-Value.png” />

Beneath is the system to transform the time into hours

=HOUR(A2)+MINUTE(A2)/60+SECOND(A2)/(60*60)

https://trumpexcel.com/wp-content/uploads/2019/12/Convert-Time-to-Decimal-with-Hour-Minute-Second-Formula.png” />

The HOUR perform merely returns the variety of hours which have elapsed within the given time, and so does the MINUTE and SECOND perform.

However since we want the ultimate worth in hours, we have to divide the minute worth by 60 (to transform it into hours) and the second worth by 60*60 (once more to transform it into hours).

Utilizing the Convert Operate

One other very easy (in all probability the simplest of the three strategies), is to make use of the CONVERT perform.

This perform takes a numerical worth (which might be the time on this case) and might convert it into hours (or minutes/seconds).

Beneath is the system that can convert time into hours:

=CONVERT(A2,”day”,”hr”)

https://trumpexcel.com/wp-content/uploads/2019/12/Convert-Time-to-Decimal-using-the-CONVERT-function.png” />

The second argument is to inform the perform of the prevailing knowledge format and the third argument is the format to which you wish to convert.

Word: Generally the outcomes might look totally different than anticipated. This could possibly be as a result of cell format which can present the lead to a date format as an alternative of decimal format. You may simply change this by going to the House tab and setting the format from the format drop-down (it’s within the Quantity group)

In observe, you’re extra more likely to get date and time collectively (as an alternative of simply the time as I’ve proven within the examples above). You may nonetheless use the strategies proven above, however you first want to separate the date and time worth after which use the strategies proven above on solely the time half. Later on this tutorial, I additionally cowl two strategies to separate date and time in Excel utilizing formulation.

You may as well use these identical strategies lined above to transform time to minutes and seconds.

Let’s shortly take a look at these examples as effectively.

Convert Time to Minutes in Excel

On this part, I cowl three other ways to transform time to minutes in Excel.

Utilizing Easy Multiplication to Get the Minute Worth

In a day, there are 1440 minutes (24*60).

So, to transform time to minutes, you’ll be able to merely multiply the time worth with 1440.

Suppose you have got the time in a cell as 6:00 PM  (in cell A2) and also you wish to convert it into minutes.

Beneath is the system to do that:

=A2*1440

https://trumpexcel.com/wp-content/uploads/2019/12/Convert-time-to-minutes-with-simple-multiplication.png” />

This will provide you with the entire variety of minutes which have elapsed on that day within the given time.

Word: Do not forget that in case you have got seconds as effectively within the time, these shall be transformed into minutes and be proven because the decimal half. For these to be seen within the outcome, that you must format the ensuing cells to indicate outcomes as much as 2/three decimal locations.

In case you solely need the minute worth and ignore the seconds half, use the under system utilizing INT.

=INT(A2*1440)

https://trumpexcel.com/wp-content/uploads/2019/12/Convert-time-to-minute-and-only-get-full-minutes-and-not-seconds.png” />

Utilizing Excel Features (HOUR, MINUTE, and SECOND)

Identical to we used these features to get the hour worth, you may as well use these to transform time to minutes.

Beneath is the system that can convert time to minutes utilizing these features:

=HOUR(A2)*60+MINUTE(A2)+SECOND(A2)/60

https://trumpexcel.com/wp-content/uploads/2019/12/Convert-time-to-minutes-using-formulas.png” />

Because the intention right here to get all of the components in minutes (hours, minutes, and seconds), we multiply 60 with hours to get the minutes worth in it and we divide seconds by 60 to get the minute worth.

Utilizing the Convert Operate

Once more, undoubtedly the simplest approach to convert time to minutes is by utilizing the convert system.

The under system takes the time worth and converts it into minutes;

=CONVERT(A2,”day”,”mn”)

https://trumpexcel.com/wp-content/uploads/2019/12/Convert-time-to-minute-using-the-CONVERT-function.png” />

The second argument tells the perform that the worth in cell A2 is in day format and the third argument (“mn”) tells it to transform it into minutes.

Convert Time to Seconds in Excel

On this part, I cowl three other ways to transform time to seconds in Excel.

Utilizing Easy Multiplication to Get the Seconds Worth

In a day, there are 86400 seconds (24*60*60).

So, to transform time to seconds, you’ll be able to merely multiply the time worth with 86400.

Suppose you have got the time in a cell as 6:32:30 PM  (in cell A2) and also you wish to convert it into seconds.

Beneath is the system to do that:

=A2*86400

https://trumpexcel.com/wp-content/uploads/2019/12/Convert-Time-to-seconds-with-simple-arithmetic-multiplication.png” />

This will provide you with the entire variety of seconds which have elapsed on that day within the given time.

Utilizing Excel Features (HOUR, MINUTE, and SECOND)

Beneath is the system that can convert time to minutes utilizing these features:

=HOUR(A2)*60*60+MINUTE(A2)*60+SECOND(A2)

https://trumpexcel.com/wp-content/uploads/2019/12/Convert-time-to-minutes-using-HOUR-MINUTE-SECOND-formulas.png” />

Because the intention right here to get all of the components (hours, minutes, and seconds) in seconds, we multiply 60*60 with hours to get the seconds worth in it and we multiply minutes by 60 to get the seconds worth.

Break up Date and Time and Then Convert Time to Decimal

To date, we have now seen examples the place we solely have the time and we needed to transform that point into hours, minutes or seconds.

However what if in case you have the date in addition to the time?

In that case, you can’t use the above strategies instantly. You’ll first have to separate the date and time after which convert the time to decimals.

Since date and time are saved as numbers in Excel, separating these will get simpler.

A full day is saved as an integer and the time half is saved as a decimal worth. So if you wish to separate date and time, you merely must separate the integer half and the decimal half.

Let’s take a look at a few very easy methods to separate the date and time in Excel.

Break up Date and Time utilizing INT Method

Suppose you have got the dataset as proven under and also you wish to cut up the date and time.

https://trumpexcel.com/wp-content/uploads/2019/12/Date-and-time-that-needs-to-be-split.png” />

To do that, enter the next system in cell B2:

=A2-INT(A2)

https://trumpexcel.com/wp-content/uploads/2019/12/Split-date-and-time-using-INT-function.png” />

The above system removes the integer half and offers you the decimal a part of the date.

Now you need to use any of the three strategies lined above (utilizing arithmetic multiplication, utilizing HOUR/MINUTE/SECOND system, or utilizing the CONVERT system) to transform the time to decimal.

Break up Date and Time utilizing the MOD Method

One other method to do that may be by utilizing the MOD perform.

Suppose you have got the dataset as proven under and also you wish to cut up the date and time.

To do that, enter the next system in cell B2:

=MOD(A2,1)

https://trumpexcel.com/wp-content/uploads/2019/12/Split-Date-and-Time-using-MOD-function.png” />

This could provide the decimal half straight away after which you need to use any of the three strategies lined above to transform time to decimal numbers (hours, minutes, or seconds).

You might also like the next Excel tutorials: