Question on manipulating "Time" in a Date/Time Field

[Cranger]

My second question of the day. I have an access query where I am reporting on Time and Time Diffences. I have 2 different Start and End Date/Time fields. One is a record of an Outage. Start of the Outage and End of an outage. The other field is the times that the system is available.

I am trying to report on the percentage of the outages. I can properly display the time in hours and minutes of the outage, but was wonder if I could take that and convert it to minutes and then divide that value by the total number of minutes possible?? Can this be done, or am I making this more difficult than it is. I am getting confused on how the times are being displayed after any mathimatical operations. HELP!

 

[tlbroadbent]

Use the datediff function to find the difference between two date/time columns in inutes, seconds, hours, days, etc.

Select datediff("n", [Outage Start], [Outage End]) As MinOutage, datediff("n", [System Start], [System End]) As MinSystem, MinOutage/MinSystem As PctOutage
From table Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[Cranger]

Thanks. That helped greatly.!

 

[MichaelRed]

Terry is correct, in the formal sense. When dealing with Date/Time data types, is can be helpful to understand the underlying data 'structure'. MS Access, (all MS products) and many other 'languages' represent dste and time values as a 'floating point' number. The "Date" part is an "integer" which represents the number of DAYS from some starting point (MS uses 12/30/1899?). Time is in the decimal / fractional part of the day - literally. So 24 hours = "1", 12 Hours = 0.5, 6 Hours = 0.25, 1 Hour = 1 / 24 ~= 4.16667E-02. So the date/time functions and data types just represent a "format" for the display of these values.

Once you get away from the display of these as "date/time", the use of the format functions becomes (with the exception of documentation / readability considerations) more of a choice than a necessity. To get your "Percentage" or Ratio, you can totally ignore the calculation of minutes (seconds, hours ....) and just 'do the math'.

(I added the " * 100" just to "Formalize" the calculation as a percentags).

([Outtage End] - [Outtage Start]) / [System End] - [System Start]) * 100 As PctOuttage MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over