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

[ykr445]

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 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.

 

[iliace]

Hi ykr - welcome to the board!

From the description of your issue, it sounds like you might be having a date/time value vs number of hours discrepancy. However, for us to help you, we will need more information - keep in mind, we cannot see your spreadsheet, so outside what you tell us, we are basically operating your spreadsheet with a blindfold.

* Can you post a sample of your data? This is usually the most important first step - either post an excerpt of your data, which includes all the possibilities you want to accommodate, or create a mock-up of your data (if you are unable to post confidential and/or proprietary information).
* It is not clear from your post how you define "idle time" between two time stamps. What are the actual columns, what do they represent? How do you "get a number"?
* What do you mean by "format" when you say "depending on the format it shows weird times"? Is this the number format of the cells/column?

Excel stores dates and times as a numeric value. The date is the number of days from January 1, 1900 - for example, April 24, 2014 = 43214. Any whole number is the number of days. Any time value is a fraction of the particular day. For example, 6pm on April 24, 2014 = 43214.75

A time value by itself will just be the fraction, so 6pm with no specific date is just 0.75 - and it is the number format setting that determines how the value is displayed. If you format 43214.75 as Time, you will see 6:00pm or 18:00. If you format 43214.75 as Date, you will see 4/24/2018. You can also see both. But the point is, the date is stored as a whole number, and the time is stored as a fraction thereof - regardless of date.

If you have a formula that is calculating the number of hours, you need to take the time value and multiply it by 24. This way you can use the result to calculate something like "total idle hours" or "hourly rate paid".

However, if you turn that back into a time value, you will not get the desired result.

Everywhere in your time/day calculation, it is important to keep track of whether you are dealing with hours/minutes/seconds (date and time value, multiplied by 24), or actual fractions of a day.

For all these reasons, in order to get a valid solution, it is important that you post some kind of sample of the data you are using, the calculations involved, and how the individual columns are formatted.