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:
Type
1440
into any blank cell. Select this cell and press Ctrl + C.Select the range that contains the minutes.
Paste Special > Values > Divide
Click OK. Result:
Select the range of number again and press Ctrl + 1.
Go to Number > Custom
Type the format:
[h] "h" mm "m"
Result:
If you'd like to change them back to minutes:
1440
into any blank cell. Select this cell and press Ctrl + C.To get the number of hours in decimal form, use
60
instead of1440
. So for 330 (mins), you'll get 5.5 (hours).