+ Reply to Thread
Results 1 to 15 of 15

Getting accurate Idle times and Average Idle times over 24 hours.

  1. #1
    Registered User
    Join Date
    08-06-2017
    Location
    Glendale, California
    MS-Off Ver
    excel
    Posts
    57

    Getting accurate Idle times and Average Idle times over 24 hours.

    I am trying to get the average idle time in-between two-time stamps. when I do so I get a number I don't believe is accurate. depending on the format it shows weird times one being 3 hours and another being 13:3:3:53 when formatted with days but I know it doesn't mean 13 is days.

    When the "Idle Time" Column is formatted with days in it I get times like 29:2:57:16 which I think means 29 hours(1 day for 5 hours) then 2 hours??? but that doesn't sound right either. Can anyone explain what these times mean and how can I get it to show accurate values? I noticed when it goes over 24 hours it resets to zero unless I change the format. I tried Elapsed hours:MM:SS but that gave me also weird stuff like 722:57:16 which I interpret as 3 days 2 hours 57 minutes but then I have cells that show 466:19:14 which I would think to mean at-least almost 2 days then 19 hours 14 minutes but I am confused as to why it is resetting before reaching 48 hours.

    I have included a sample sheet of my troubles. I am trying to get the Idle column and the cell with the average formula to show accurately days, hours, minutes, seconds and any formatting I try is not working. Can someone please help. I am sure it is something small but I am getting stuck and any google search for answers I have tried has not helped.
    Attached Files Attached Files

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Getting accurate Idle times and Average Idle times over 24 hours.

    is that what you want?
    done with PowerQuery and simple AVERAGE()

  3. #3
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Getting accurate Idle times and Average Idle times over 24 hours.

    Dates and Times in Excel are stored as numbers, and only appear as "8:32 AM" or "Monday, March 18, 2018" due to formatting. For example:

    The number 0.0534 in a cell formatted as time, shows "1:16:54 AM". This is because there are 86,400 seconds in a day, and 86,400 * 0.0534 = 4,613.76 seconds. Adding 4,613.76 seconds to midnight (0), results in 1:16:54 AM.

    Dates are numbers representing the number of days since January 1, 1900. So the value 1 represents 1/1/1900, while 43214 represents today (it has been 43,214 days since 1/1/1900).

    Your formatting attempt using "dd", though, is misguided. The "m", "d" and "y" formatting types also reflect 'dates starting from 1/1/1900'. If you type 1 into a cell and format it as "dd", you'll simply return "01". If you type 30, you'll get 30 - because day 30 of the Excel calendar is January 30, 1900. If you type 40, though, the result will be "09" - since February 9 is the 40th day after 1/1/1900.

    Rather than get too complicated, here are a few links for dealing with Date/Time formats:

    http://www.contextures.com/xlfaqDat.html
    https://support.microsoft.com/en-us/...times-in-excel
    https://support.microsoft.com/en-us/...lls-dialog-box

    Hope that helps!

  4. #4
    Registered User
    Join Date
    08-06-2017
    Location
    Glendale, California
    MS-Off Ver
    excel
    Posts
    57

    Re: Getting accurate Idle times and Average Idle times over 24 hours.

    Hi Sandy666. I noticed in your cell J2 the time shows 2.02:57:00. I know the idle time should show 3 days 3 hours but your's(from my understanding) is showing 2 days 3 hours which isn't right. Am I misinterpreting it or is there another way to get the idle time?

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Getting accurate Idle times and Average Idle times over 24 hours.

    J2 shows: 3.02:57:00

    j2.jpg

  6. #6
    Registered User
    Join Date
    08-06-2017
    Location
    Glendale, California
    MS-Off Ver
    excel
    Posts
    57

    Re: Getting accurate Idle times and Average Idle times over 24 hours.

    Hi Sandy, thats odd. Ill include a screenshot of what i am seeing. I'm curious as to why. Out of curiousity how did you combine cells AB and DE? Was it with =concatene or another function?
    Attached Images Attached Images
    Last edited by yourik; 04-24-2018 at 07:26 PM.

  7. #7
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Getting accurate Idle times and Average Idle times over 24 hours.

    maybe any problem with date format , US style vs UK style

    I changed to US style but I'm not sure it will work (depends of system locale)

    I suggest create that query by yourself and check how it works
    Last edited by sandy666; 04-24-2018 at 07:30 PM.

  8. #8
    Registered User
    Join Date
    08-06-2017
    Location
    Glendale, California
    MS-Off Ver
    excel
    Posts
    57

    Re: Getting accurate Idle times and Average Idle times over 24 hours.

    Excel has varying results if you are in the UK or US?? I am US by the way.

  9. #9
    Registered User
    Join Date
    08-06-2017
    Location
    Glendale, California
    MS-Off Ver
    excel
    Posts
    57

    Re: Getting accurate Idle times and Average Idle times over 24 hours.

    I am still seeing the same numbers in the same cells, it's really odd. Out of curiousity how did you combine cells AB and DE? Was it with =concatene or another function? Maybe I can just copy paste the function you sued to get the results you're seeing.

  10. #10
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Getting accurate Idle times and Average Idle times over 24 hours.

    did you read a little info : done with PowerQuery?

    Power Query for
    here is a simple Range , not Table or Query Table (but without any calculation of the time/days - just data only)
    Last edited by sandy666; 04-24-2018 at 07:37 PM.

  11. #11
    Registered User
    Join Date
    08-06-2017
    Location
    Glendale, California
    MS-Off Ver
    excel
    Posts
    57

    Re: Getting accurate Idle times and Average Idle times over 24 hours.

    Hi Sandy,

    Ill be honest man that seems very complicated and I simply don't understand it (Im not smart on excel). I was thinking their might be a simple function and format change that would give me the accurate average of idle times and also display the time when it goes over 24 hours. Is there no such thing?

  12. #12
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Getting accurate Idle times and Average Idle times over 24 hours.

    ok, check on sheet 2
    formulas and custom formats

    then AVERAGE column J and set custom format dd.hh:mm:ss

    i changed file - it contain over 24 (with appropriate custom format)
    Last edited by sandy666; 04-24-2018 at 08:20 PM.

  13. #13
    Registered User
    Join Date
    08-06-2017
    Location
    Glendale, California
    MS-Off Ver
    excel
    Posts
    57

    Re: Getting accurate Idle times and Average Idle times over 24 hours.

    Ok, that worked. Thank you so much for the help. I'm disappointed in me for not trying something as simple as the sum and that format. It's always a simple solution that solves it. Again thank you. I really appreciate it.

  14. #14
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Getting accurate Idle times and Average Idle times over 24 hours.

    you are welcome

    you need to remeber only your dates and time must be a Excel date and time not a text because dates and time are a numbers

    If that takes care of your original question, & to say Thanks, please
    1. click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
      then
    2. select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
    If you did it already - ignore it.
    Thank you.

  15. #15
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Getting accurate Idle times and Average Idle times over 24 hours.

    I tried another way. In both G2 and G3 of your upload array enter this formula. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The format according to your upload.

    It is as previously stated above. Dates and times are numbers. In cell C2 30.12310185 is the underlying numeric value of 1/30/1900 2:57:16 AM. Formatting is cosmetic. The integer part is the number of full days from 1/1/1900. The decimal portion is the fractional part of a day. For ex: 8/24 is 1/3 of a day. Formatted for time 8:00 AM

    =MOD(C2,1) extracts the decimal portions from those numbers in column C and then averages them. Remember to enter with Ctrl + Shift + Enter.

    I get

    0:11:45:46 (DD:HH:MM:SS)
    11:45:46 (HH:MM:SS)
    Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. if or iferror or other formula help needed to count idle hours.
    By Rinkojhon in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-09-2017, 10:33 AM
  2. If a search term can be found 1 time, 2 times 3 times 4 times 5 times
    By excelcandy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-10-2013, 09:57 PM
  3. Idle time
    By frequent391 in forum Excel General
    Replies: 1
    Last Post: 07-24-2011, 03:57 PM
  4. Macro Loop – Find Specific Times, Extract Date, Average Values of Times
    By ExcelQuestFL in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-25-2010, 02:50 PM
  5. Macro to help sort date - times and average similar times.
    By ferretydeath in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-26-2008, 05:44 PM
  6. Idle timeout
    By Jesse in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-20-2006, 11:15 AM
  7. Calculation of hourly rate times hours times 1.5
    By Newbusinessbod in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-06-2005, 12:50 PM

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