+ Reply to Thread
Results 1 to 4 of 4

Calculating a percentage of a given time span

  1. #1
    Registered User
    Join Date
    05-29-2008
    Posts
    3

    Calculating a percentage of a given time span

    I have a weekly data report that is generated with one column containing how long a given task took (in hours) and I need switch that to minutes and then I need to discover the percentage time of the week (business days so 7200 minutes) it took to complete the task.

    I converted the hour to minutes by just going to Format-->Number and doing a custom entry of [m], but when I type in the simple formula to get the percentage =A2/7200 I'm coming back with very faulty data.

    The problem I'm encountering seems to stem from the fact that even though my time column visibly appears to have changed to minutes when I click on a cell and look in the formula bar it reads as "1/3/1900 1:30:53 PM" (the serial date I believe?) and then my average formula is using that to calculate the percentage, which is not working at all.

    Is there a way I can make the time presented to me as the serial date have the actual minute value so I could use the data in a formula?

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,610
    You need to provide a better explaination of your problem:
    In the US we work a 40 hr week which is only 2400 minutes not 7200 minutes.
    Five 24 hour days will give 7200 minutes but that means the task is being worked by three shifts of workers per day. Also, why convert 120 hours (a looong work week) to 7200 minutes? If a task takes 11.2 hours to complete and the standard workweek is 40 hours then 11.2/40*100 gives the percentage of the week spent on the task (28%). Please explain why your numbers are different...
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    05-29-2008
    Posts
    3
    Let's see if I can clear this up a bit:

    The company I work with provides tech support 24/7/365. A customer of ours wants to know when one of their servers goes down how long (in minutes because that's what they asked for...don't know why they want minutes in particular) it takes us to restore it and they also want to know how long the server was down for the week (in percentage format). The current date period of the report I'm running is a Monday-Friday report, hence the 5 days and 7200 minutes.

    I have a company program that records the amount of time (in a hh:mm:ss format) it takes to restore service. My problem is that when I export that information to an Excel spreadsheet, Excel is not reading 8:24:25 as 8 hours 24 minutes and 25 seconds, but rather as 8:24:25 AM. i.e. it's giving it a time stamp and not reading it just as a set number of hours, minutes, and seconds.

    I didn't think this would matter, but I cannot get my formula to give a correct answer when it has that time stamp on it and I need to know if it's possible to get rid of said time stamp.

    I've attached a sample spreadsheet that lists a few examples. Column A is the original data generated in hours, Col B is that data converted to minutes, Col C is the percentage I get when I use the =B2/7200, and Col D is the percentage I get when I manually typed in the number =504/7200

    I hope I better explained my problem and thanks for the help!
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,610
    Try the formula:
    Please Login or Register  to view this content.
    It worked on the sample sheet provided.
    Attached Files Attached Files
    Last edited by protonLeah; 05-30-2008 at 06:59 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1