Excel – Converting “in place” a column of cells from minutes to hours and minutes in Excel

conversionmicrosoft excelmicrosoft-excel-2010worksheet-function

I have several columns of data in an Excel 2010 worksheet that are values in minutes and I want to convert them to hours and minutes (the data source will only generate an Excel workbook using minutes).

How can I convert these values in place to a text string that is hours and minutes.

For example, if I have a cell F1 containing a value 300 and drop this formula into into the same cell I get a circular reference error:

=INT(F1/60)&" h "&MOD(F1,60)&" m"

I could hide the cells containing the raw values and have a second column that has the formula that does the conversion, but it means an awkward extra step for five long columns of numbers.

Is there a way to do this without creating extra cells to hold the calculated value?

Best Answer

I agree with Excellll; you're going to need VBA to convert the values in place to a string value.

This method won't result to text strings; but it might help you get the format you need.

Let's say these were my minutes:

enter image description here

  1. Type 1440 into any blank cell. Select this cell and press Ctrl + C.

  2. Select the range that contains the minutes.

  3. Paste Special > Values > Divide

  4. Click OK. Result:

    enter image description here

  5. Select the range of number again and press Ctrl + 1.

  6. Go to Number > Custom

  7. Type the format:

    [h] "h" mm "m"

Result:

enter image description here

If you'd like to change them back to minutes:

  1. Type 1440 into any blank cell. Select this cell and press Ctrl + C.
  2. Select the range that contains your hours/times.
  3. Paste Special > Values > Multiply.
  4. Press Ctrl + 1. Set the number format back to General.
  5. You may get rid of the 1440 cell when you're done.

To get the number of hours in decimal form, use 60 instead of 1440. So for 330 (mins), you'll get 5.5 (hours).

Related Question