excel percentage time formula

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum 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

resolvedResolved · 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

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!

 

Training courses

 

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Formula for last day of month

In 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

View all Excel hints and tips


Server loaded in 0.08 secs.