98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Excel Percentage Time formula calculation | Excel forum
Excel Percentage Time formula calculation | Excel forum
Resolved · Medium Priority · Version 2007
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
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 forumATstl-training.co.uk (replacing AT with @) for my attention.
Kind regards
Amanda
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
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 tip:Formula for last day of monthIn some cases it is necessary to find the last day of a month for a given date. If you use the following formula, you can achieve this, ie; if you have a column of dates, use this formula to find the end of month for each day by using the fill handle. The formula is as follows, and assumes in this example that the first date in question is in cell C5, in any other cell type; =DATE(YEAR(C5),MONTH(C5)+1,1)-1 |