Tech Support Guy banner
Status
Not open for further replies.

Display minutes as hours + calculate

14K views 17 replies 5 participants last post by  DCreature 
#1 ·
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!!!!
 
See less See more
#2 ·
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. :up:
 
#4 ·
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
 
#6 ·
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!!!!
 
#7 ·
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 :-(
 
#8 ·
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
 
#10 ·
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
 
#11 ·
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 ;-)
 
#14 ·
hi DCreature!

:eek: 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... :D
 

Attachments

#15 ·
hi DCreature!

:eek: 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... :D
 
#17 ·
OK, thanks that worked :) now I am trying to do another worksheet which will contain a line chart to show volume of hours worked per week over time over a few weeks, however, the data source value entered is like this:

='16-20.August.2004'!$C$3:$C$14

and what it does is it have a line with points everywhere, I want this to be just one dot, so when I add next value from next week the line will either go up or down, hope you know what I meant, I put this formula in but Excel says its not correct :-(

='16-20.August.2004'!=SUM($C$3:$C$14)

It suppose to sum up the total hours worked in the column C and display it as a DOT or line that goes to a single dot on the chart :-(

I am sure you know how to do this ? Cheeeeeers!
 
#18 ·
OK, thanks that worked :) now I am trying to do another worksheet which will contain a line chart to show volume of hours worked per week over time over a few weeks, however, the data source value entered is like this:

='16-20.August.2004'!$C$3:$C$14

and what it does is it have a line with points everywhere, I want this to be just one dot, so when I add next value from next week the line will either go up or down, hope you know what I meant, I put this formula in but Excel says its not correct :-(

='16-20.August.2004'!=SUM($C$3:$C$14)

It suppose to sum up the total hours worked in the column C and display it as a DOT or line that goes to a single dot on the chart :-(

I am sure you know how to do this ? Cheeeeeers!
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top