Excel: Convert hours and minutes to minutes
August 2, 2017Converting hours and minutes (hh:mm) to minutes in an Excel spreadsheet is actually quite simple, once you know what to do. But I had to do quite a bit of trial and error and Googling to get it to work.
To convert hours and minutes to minutes, you have to multiply the hh:mm value by 1440 (which is 24 [number of hours in the day] multiplied by 60 [number of minutes in an hour]), AND make sure you set the formatting correctly for the both the hh:mm cells and the resulting minute cells. This is where I got caught — I didn’t have the correct formatting applied to the cells. Once I got that right, it all worked.
Here’s how…
- Enter elapsed times in hours and minutes (using the format hh:mm) in Column B. (Yes, those non-stop flights to/from Sydney to Dallas Fort Worth are killers!)
- Set the format for this column to Custom > h:mm. (To format the column, select the column header, right-click on it, select Format Cells, select Custom on the Number tab, then select h:mm from the list of types. Click OK.)
- Insert a new column (C) and called it Minutes.
- Set the format for the cells in this new column to Number with no decimal places — this formatting is critical for the formula to work. (To format the column, select the column header, right-click on it, select Format Cells, select Number on the Number tab, then change the Decimal Places value to 0. Click OK.)
- Put the cursor in the first cell in the new column that pairs with a cell in the hh:mm column. In my example, that was C3, which pairs with B3.
- Type =B3*1440 in the formula bar, then press Enter to convert the hours and minutes into minutes.
- Click in the C3 cell and ‘grab’ the bottom right handle of the cell marker (it turns to a + sign when you’ve grabbed it correctly) and drag it down the other cells in column C. (See below for how to apply it to ALL cells in the column.)
- When you release the mouse, all those cells you dragged this formula over will be converted to minutes and seconds.
To apply this formula to the entire column:
- Copy (Ctrl+C) the result in the first cell with the formula (C3 in my example).
- Select the entire column (column C in my example) by selecting the column header.
- Paste (Ctrl+V).
- You’ll have to rename the column back to Minutes, but you’ll have that formula now applied to every row of your spreadsheet for that column.
These sites helped me figure out what to do:
- http://www.proworkflow.com/blog/2009/02/converting-time-into-minutes-using-excel/
- http://mathforum.org/library/drmath/view/63521.html
See also:
- Convert seconds to minutes: https://cybertext.wordpress.com/2012/04/27/excel-convert-seconds-to-minutes/
[Links last checked August 2017]
Thank you for the tip!!
by Teo February 17, 2019 at 9:46 amThank you, well hidden by MS, but fortunately, well explained by You! :)
by Michal Mochon March 14, 2019 at 10:07 pmthank you very much for explaining how to do this I have been tring to do this for quite a while.
by skip April 16, 2019 at 11:30 pmStrangely, some of my calculations are incorrect with the formula. For example, I have 0:05 but I’m getting a result of 1445 minutes. Please help. The majority of my results are correct.
by Andrew Arbogast June 20, 2019 at 9:52 pmHi Andrew
I tested my steps again, adding 0:05 to the list of times. It worked without an issue. I also tried changing the formatting of that 0:05 cell to all other options, but the worst I got was a result of 72 or a value error message.
Because you got a result that’s the sum of 5 and 1440, can you put your cursor in the results cell for 0:05 and check the math symbol in the formula at the top? It should be =*1440, but it sounds like you might have =+1440.
BTW, I tested in Excel 2010 for Windows, though I doubt that matters.
–Rhonda
by Rhonda June 21, 2019 at 6:18 amThanks for this shortcut!
by ElizaJoyce August 16, 2019 at 11:38 pmThank you so much! This worked beautifully! I appreciate the tutorial!
by Jessica October 10, 2019 at 4:24 amThanks for your clear explanation – I have been trying to do this for a while and MS is no help
by Sally Bound October 11, 2019 at 10:24 amThis is wonderful, and exactly what I needed. Thanks so much!
by Kellie Bauman January 24, 2020 at 3:48 amClear explanation. This helped me with a project today. Thank you!
by Ava Baker June 24, 2020 at 2:33 amThank you very much for the steps. Can you please help me understand the logic behind multiplying the time with 1440?
by niladri September 22, 2020 at 5:50 pmHi Nilandri
The beginning of paragraph 2 explains it: 1440 is the number of minutes in 24 hours (60 x 24).
–Rhonda
by Rhonda September 23, 2020 at 6:10 amThis is very well explained – thank you :)
by Linda March 11, 2021 at 8:05 amMany many tnx.
by Engr Mostafizur Rahman April 10, 2021 at 12:22 amThank you!
by Yocheved June 9, 2021 at 4:19 amVery much appreciated.
by George Dirani September 18, 2021 at 4:40 amThank you!
by Julie October 6, 2021 at 2:38 amThanks for your help. Perfect!
by Norman December 11, 2021 at 7:39 pmThank you
by andree p March 4, 2022 at 8:10 pmThank you. The explanation is clear. I would never have thought of formatting the minutes as a number with no decimals.
by Martin SELBY September 1, 2022 at 8:48 pmSo well explain and helpful thank you.
by Andile November 23, 2022 at 6:12 pmThank you so much !!!! :)
by Khushbu February 2, 2023 at 5:54 pm1440 for what?
by shahzamankhaskheli May 31, 2023 at 6:21 pmThe beginning of paragraph 2 explains it: 1440 is the number of minutes in 24 hours (60 x 24).
–Rhonda
by Rhonda June 1, 2023 at 6:18 am