4

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?

Improve this question
1
  • 1
    The short answer is "no" if you're committed to using only worksheet functions. However, if you're open to using VBA, a very simple macro can do this.
    – Excellll
    Commented Sep 28, 2011 at 20:00

2 Answers 2

Reset to default
5

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).

Improve this answer
2
  • Brilliant!...I didn't know you could do that "paste special and divide". I did write a VBA macro, but because macros have to belong to a workbook I had to recreate it every time I did an export from Jira. Also the number of rows varies depending on the number of project tasks etc etc. And PM's need to be able to work this as well :). Very neat, thanks.
    – Kev
    Commented Sep 29, 2011 at 11:45
  • You might find this article useful too: office.microsoft.com/en-us/excel-help/…
    – Ellesa
    Commented Sep 29, 2011 at 13:22
0

The circular reference is because you are trying to run the formula on the cell the data is in. You need to place the formula in a different cell so it can calculate the results. If you don't want to see the first cell (total minutes), you can hide the column. I tested this with 300 in F1 and placing your formula in F2 and it works properly.

Improve this answer
1
  • As I stated in my question, I already know how to do that.
    – Kev
    Commented Sep 28, 2011 at 20:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .