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
