Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Display minutes as hours + calculate


(!)

DCreature's Avatar
DCreature DCreature is offline
Member with 117 posts.
THREAD STARTER
 
Join Date: Jun 2004
Location: Australia, Queensland
Experience: Advanced
19-Aug-2004, 05:50 AM #1
Display minutes as hours + calculate
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's Avatar
DCreature DCreature is offline
Member with 117 posts.
THREAD STARTER
 
Join Date: Jun 2004
Location: Australia, Queensland
Experience: Advanced
19-Aug-2004, 06:42 AM #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.
DaveBurnett's Avatar
DaveBurnett   (Dave) DaveBurnett is offline DaveBurnett is a Trusted Advisor with special permissions. DaveBurnett has a Profile Picture
Computer Specs
Trusted Advisor with 9,690 posts.
 
Join Date: Nov 2002
Location: Polesworth, UK
Experience: Advanced
19-Aug-2004, 07:43 AM #3
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's Avatar
Member with 4,694 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
19-Aug-2004, 10:28 AM #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

Last edited by Rollin_Again; 19-Aug-2004 at 10:39 AM..
DaveBurnett's Avatar
DaveBurnett   (Dave) DaveBurnett is offline DaveBurnett is a Trusted Advisor with special permissions. DaveBurnett has a Profile Picture
Computer Specs
Trusted Advisor with 9,690 posts.
 
Join Date: Nov 2002
Location: Polesworth, UK
Experience: Advanced
19-Aug-2004, 11:40 AM #5
and you can still do the maths on it.
DCreature's Avatar
DCreature DCreature is offline
Member with 117 posts.
THREAD STARTER
 
Join Date: Jun 2004
Location: Australia, Queensland
Experience: Advanced
20-Aug-2004, 11:06 AM #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!!!!
DCreature's Avatar
DCreature DCreature is offline
Member with 117 posts.
THREAD STARTER
 
Join Date: Jun 2004
Location: Australia, Queensland
Experience: Advanced
20-Aug-2004, 11:17 AM #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 :-(
Zack Barresse's Avatar
Computer Specs
Member with 5,434 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
20-Aug-2004, 11:34 AM #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
DaveBurnett's Avatar
DaveBurnett   (Dave) DaveBurnett is offline DaveBurnett is a Trusted Advisor with special permissions. DaveBurnett has a Profile Picture
Computer Specs
Trusted Advisor with 9,690 posts.
 
Join Date: Nov 2002
Location: Polesworth, UK
Experience: Advanced
20-Aug-2004, 11:36 AM #9
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's Avatar
Member with 4,694 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
20-Aug-2004, 12:17 PM #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

Last edited by Rollin_Again; 20-Aug-2004 at 02:21 PM..
DCreature's Avatar
DCreature DCreature is offline
Member with 117 posts.
THREAD STARTER
 
Join Date: Jun 2004
Location: Australia, Queensland
Experience: Advanced
22-Aug-2004, 10:19 AM #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 ;-)
BlackXenon's Avatar
BlackXenon BlackXenon is offline
Junior Member with 3 posts.
 
Join Date: Aug 2004
Location: Myanmar (Burma)
Experience: Intermediate
23-Aug-2004, 07:10 AM #12
* 1 thing to consider
if xxx/1440 formula in total hr. of a job > 24 hr it will reset from zero hr.
DCreature's Avatar
DCreature DCreature is offline
Member with 117 posts.
THREAD STARTER
 
Join Date: Jun 2004
Location: Australia, Queensland
Experience: Advanced
23-Aug-2004, 04:37 PM #13
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's Avatar
BlackXenon BlackXenon is offline
Junior Member with 3 posts.
 
Join Date: Aug 2004
Location: Myanmar (Burma)
Experience: Intermediate
23-Aug-2004, 11:06 PM #14
Lightbulb check the attached zip.
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...
Attached Files
File Type: zip CT.zip (2.8 KB, 94 views)
BlackXenon's Avatar
BlackXenon BlackXenon is offline
Junior Member with 3 posts.
 
Join Date: Aug 2004
Location: Myanmar (Burma)
Experience: Intermediate
23-Aug-2004, 11:06 PM #15
Lightbulb check the attached zip.
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...
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑