Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel Percentage Time formula calculation
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel Percentage Time formula calculation
Resolved · Medium Priority · Version 2007
Reply Mon 3 Nov 2008, 12:57Delegate Stuart said...
Stuart has attended:
Excel Intermediate course
Excel Advanced course
Excel Percentage Time formula calculation
Hi Guys
I have a spreadsheet where I have a list of people and the time spent logged into phones, and within that time, how much time they spend active, on hold, etc. How can I create a formula to give me a % amount for the time spent on hold out of the total time logged in? Currently the raw data in the cells is in the format "21h 21m 26s". I am doing the normal divide smaller number by larger but the formula is not working.
thanks
For upcoming training course dates see: Pricing & availability
RE: Excel Percentage Time formula calculation
Hi Stuart
Thank you for your question.
I think this will have something to do with how the data is entered into the spreadsheet - if you have a time entered as you've described (21h 21m 26s), Excel will likely read this as text instead of a number, meaning you can't then use it in a calcuation/formula.
If you would like to email me a bit of sample data, I can have a look at it and see if I can come up with something.
Please email to forumATmicrosofttraining.net (replacing AT with @) for my attention.
Kind regards
Amanda
Reply Wed 5 Nov 2008, 09:12Delegate Stuart said...
RE: Excel Percentage Time formula calculation
Hi Amanda
thanks very much for that. That was just the clue I needed - I've changed the format from 21h 21m 26s to 21:21:26 and the formula works fine with normal arithmetic operaters. The only down side is I have to manually go through and change the values, ah well!
Thanks
RE: Excel Percentage Time formula calculation
Hi Stuart
It may be possible to use the Replace feature to help you with this (rather than having to make all the manual replacements yourself) - hopefully you get this in time and you haven't done it all manually yourself by now!
If you go to the Home ribbon, click the button which I think is called Find and Select (at the right hand side of the ribbon), and select the Replace option (or use Ctrl + H to open the Replace dialogue box).
Then in the Find box, enter h followed by a space (which you can enter by pressing the spacebar once); and in the Replace box, type in :
Essentially this will ask Excel to find h followed by a space, and replace this with : instead
If you click Find Next and then Replace, you can check to see if Excel is making the replacements correctly; then if you feel comfortable with this (i.e. it's working!) then you can click Replace All to make all the replacements quickly.
Repeat to replace m followed by a space, and s followed by a space with :
I hope this helps.
Amanda
Reply Wed 5 Nov 2008, 12:21Delegate Stuart said...
RE: Excel Percentage Time formula calculation
Thanks again Amanda, you are indeed in time. I shall certainly give that a go, can't see why it wouldn't work.
Thanks again!
|