Convert hh:mm:ss to Hours, Minutes, Seconds in Excel

I was stuck trying to figure out some Excel formulas because my data set decided to capture how long something takes into the format hh:mm:ss. At the same time, Excel and many other programs view that format as time of day.

This is for example, how long did it take you to build the fence? 10:05:30. As in 10 hours, 5 minutes, and 30 seconds. Excel will read this as 10:05AM and 30 seconds. Doing analysis on is strange because people really just want to know how long it took in terms of minutes or hours and adding times will not produce the results you would like. Adding time works but when you would like to add 15 minutes to hh:mm:ss it can get complicated.

Here’s the quick and dirty solution

In order to get from hh:mm:ss to a solid number you can use the following formulas:

hh:mm:ss * 24 = TOTAL NUMBER OF HOURS (you might want decimal points)

hh:mm:ss * 1440 = TOTAL NUMBER OF MINUTES (you might want decimal points)

hh:mm:ss * 86400 = TOTAL NUMBER OF SECONDS

To take out the individual hours, minutes, seconds

In Excel, if you just want the hours from hh:mm:ss, the formula is:

=hour(cell where the data is located)

In Excel, if you just want the minutes from hh:mm:ss the formula is:

=minute(cell where the data is located)

In Excel, if you just want the seconds from hh:mm:ss the formula is:

=second(cell where the data is located)

Here’s the long way to convert time

Similar Articles

Comments

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Recent Posts

Featured

Most Popular

Super Mario Bros Wii – Mushroom Houses

I was wondering how to get all the matches in on the mushroom houses in Super Mario Bros Wii. Sure enough there are patterns...

Zipcode to City, State Excel Spreadsheet

I just decided to do this. I've been meaning to learn more about the vlookup function in Excel. I figured this one would be...

Community Service Letter Template

I’ve been asked on occasion by parents if their child can volunteer some hours in the non-profit organization I help out at. I guess...