[DCreature]

Please help me. I am trying to display minutes as hours in Excel cell. For example:

(Notes: I am doing this in Excel 2003, but will be used for reporting purpose at work where they use Excel 2000)

1) User entered data of "190" which is minutes
2) Formula or codes whatever possible will convert it to "3 Hours 10 Minutes"
or "1 Hour 10 Minutes" if data entered was "70"

While still being able to display these, I would like another cell to calculate the total number of hours spent by adding up the number of minutes spent on each job:

For example, job 1 takes 70 mins (1 hour 10 mins) and job 2 takes 90 mins (1 hour 30 mins), these should adds up to 160 mins, my potential problem is that if I have each of these time cells displays as Hours and Minutes, will it lose the abiblity to be calculated becos it does not display as 90 but instead will be displayed as "1 Hour 30 Mins"..? and then the total field will convert that final MINUTES value into Hours and Minutes value.

I wonder if this is possible and how, in my opinion this is possible by coding, but I dont know how to start the code, where to click and stuff, if anyone have done these before, please please please point me to the right directions.

THanks!!!!

[DCreature]

OK sorry, lets change that to a simpler version (PS. if you can do the hard version then its great too :-))

I will have sum to sum up all the minutes from all jobs, then just perform one conversion for display in Hours and Minutes instead of on all of them :-) that should be much more easier.

[DaveBurnett]

It may be a lot easier if you can get the input in hh:mm format.
I can supply a sample time sheet based on hh:mm format if you want.

[Rollin_Again]

Divide the total number of minutes by 1440 and format the result as time or with a custom format.

For example: 190/1440 = .131944 ----> which formatted as time (HH:MM) appears as 3:10 (three hours : ten minutes)

or if you format the result with a custom format of h"hr" mm"min";@ the answer will appear as 3hr 10min



Rollin

[DaveBurnett]

and you can still do the maths on it.

[DCreature]

Rollin_Again, oh my lord, you're an Excel God!!!!!!!!!!!!!!!
Thanks a billion and nine hundred millions!!!

Where did you get that formula? I've searched the Internet for the total of probably 50 hours or so and nothing came up that works...!! Any other cool tips you can share ? or maybe compile into a file and share with us :-)

Thanks champ!!!!

[DCreature]

Rollin_Again, mate, I need one more help with your superb expertise.
The formula you gave did work perfect to sum up all minutes of a range of cells.
Then perform the division and format it to hours.

However, those range of cells that users will enter data into.. eg: 50, 60, 20, etc.
I want to format it so that it shows up as hours and minutes, however, I cannot create the circular reference or something like that, plus its confusing. Because if user enter data in the cell, it will get rid of the formula and whatever number user entered stays, eg. user entered 50 as in 50 minutes, then number 50 stays, and it wont convert to "50 Minutes" or "1 Hour 0 Minute" and so on :-(

Help please :-(

[Zack Barresse]

If you're going to input this as a number, like 70 (General formatting) you could use a formula like this ...

=INT(A1/60)&" hours "&ROUND(A1-INT(A1/60)*60,3)&" minutes"

This will put it at something like ...

1 hours 10 minutes

This would be more difficult to use in calculations, but it can be done. HTH

[DaveBurnett]

Which is why I said it would be easier if you got them to enter in hh:mm format.
see: http://homepage.ntlworld.com/burn-it...s/timetime.xls

[Rollin_Again]

Will the range of cells that the numbers be entered into always be the same? If so, what you are wanting to do can be accomplished with some simple VBA code (Visual Basic for Applications.) This code would be executed each time a number is entered into a predefined cell or range of cells. It's very simple to do but you must include logic (cell addresses) so that the application knows which cells to perform the action on and which ones not to. Does this sound like an option for you? You also have the option of selecting the cells containing the raw data that need conversion and then running a generic macro that will be executed only on the cells that are currently selected on the worksheet. In order for both these option to work you'll need to have macros enabled in Excels security settings.

Another option is to create a custom function that will convert the numbers for you. This requires the user to include the function name when entering data in the cell. For example, you could create a custom function called ConvertTime. Then, instead of entering the number 90 into a cell, the user would enter =ConvertTime(90) into the cell and it would appear in the format you want it. Of course you can name the function anything you want as long as the name is not reserved by Excel for something else.

The last option I can think of it to make an identical copy of the worksheet you are using and use one to hold the unformatted data and the other to hold the formatted data. The first worksheet would serve as an input form for the user to enter raw data is entered into. The second worksheet would then have formulas referencing the corresponding matching cells on Sheet1. For example, you enter the number 90 in cell A1 on Sheet1. Then in Sheet2 cell A1 you would have the formula =Sheet1!A1/1440. After entering all unformatted data in Sheet1, you would have matching formatted data in Sheet2. If you want to keep everything on one sheet only, you can always add a helper column and use formulas in the helper column that use the same referencing principle.

Does that make sense to you?


Rollin

[DCreature]

None of them seems like an option for Rollin~ the number of jobs per week is not fixed, it depends how many times our techies go out when ppl call us.

Option 2 would not be accepted by the headquater becuase they are quite cautious about Macro security.

Option 3 where user have to enter Converttime() would work also not work (though I really like it becuase of the custom function seems great) for the users, it would be more of a hassle to them than having impression that the sheet is making their lives easier, if it can be done without compromising the way user enter the data it would DEFINITELY be the one I choose.

I would really love it if the option 3 can be tampered so that the =ConvertTime(x) can be done in the background and user can just enter data into cells and it automatically gets converted / displayed as minutes / hours like how the x/1440 then format to time format. But again the ranges of cells in a column is uncertain becuase there might be 5 jobs per week or 20 jobs, who knows :-) well, thank you for your help.

If the option 3 can be modified like I said would you mine helping me looking into it again? but if not then its ok, thank all for your kind help ;-)

[BlackXenon]

* 1 thing to consider
if xxx/1440 formula in total hr. of a job > 24 hr it will reset from zero hr.

[DCreature]

Thanks, is there a way to fix this ?? Eventhough no jobs will exceed 24 hours but it is good idea to make the correct formula. Cheeers

[BlackXenon]

hi DCreature!

ur digging the brain but i love to answer it.

one way to fix that defect is just add a logical formula and text concatenation into a cell instead of << xxx/1440 plus h "Hr" : m "Min." custum format >>.
the formula is
=ROUNDDOWN(SUM(Time_spent)/60,0) &" Hr :" &MOD(SUM(Time_spent),60) & " Min"

cool...

[BlackXenon]

hi DCreature!

ur digging the brain but i love to answer it.

one way to fix that defect is just add a logical formula and text concatenation into a cell instead of << xxx/1440 plus h "Hr" : m "Min." custum format >>.
the formula is
=ROUNDDOWN(SUM(Time_spent)/60,0) &" Hr : " &MOD(SUM(Time_spent),60) & " Min"

cool...