h1

Excel: Convert hours and minutes to minutes

August 2, 2017

Converting 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 multiple 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…

  1. 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!)
  2. 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.)
  3. Insert a new column (C) and called it Minutes.
  4. 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.)
  5. 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.
  6. Type =B3*1440 in the formula bar, then press Enter to convert the hours and minutes into minutes.
  7. 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.)

  8. 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:

  1. Copy (Ctrl+C) the result in the first cell with the formula (C3 in my example).
  2. Select the entire column (column C in my example) by selecting the column header.
  3. Paste (Ctrl+V).
  4. 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:

See also:

[Links last checked August 2017]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: