Time formula help needed

[locke]

I work in the airfield industry and use access to track flight hours.
Doing this manually is sometimes time consuming especially when I need
to train new personnel.

Could someone help me out with a formula for figuring out the
difference between two flight times. ie take-off and land

Here's the tricky part, all times must be entered in 24 hour clock
standard and use a different type of conversion.

ex. The flight time for an aircraft that takes off at 2142 one evening
and lands at 0624 the next morning is 8.7 hours.

Here's the conversion table:

01 or 02 min - .0 hr
03 thru 08 min - .1 hr
09 thru 14 min - .2 hr
15 thru 20 min - .3 hr
21 thru 26 min - .4 hr
27 thru 33 min - .5 hr
34 thru 39 min - .6 hr
40 thru 45 min - .7 hr
46 thru 51 min - .8 hr
52 thru 57 min - .9 hr
58 thru 60 min - next whole hour

 

[Guest]

Hi Locke,

Try the following SQL statement. I used a table named "tblFlights", with two
date / time data type fields named "TakeOff" and "Landing":

SELECT tblFlights.TakeOff, tblFlights.Landing,
[FlightTimeWholeHr]+[FltTimeSwitch] AS TotalFltTime,
Int(DateDiff("n",[TakeOff],[Landing])/60) AS FlightTimeWholeHr,
DateDiff("n",[TakeOff],[Landing]) Mod 60 AS FTRem,
Switch(
[FTRem] Between 0 And 2,0,
[FTRem] Between 3 And 8,0.1,
[FTRem] Between 9 And 14,0.2,
[FTRem] Between 15 And 20,0.3,
[FTRem] Between 21 And 26,0.4,
[FTRem] Between 27 And 33,0.5,
[FTRem] Between 34 And 39,0.6,
[FTRem] Between 40 And 45,0.7,
[FTRem] Between 46 And 51,0.8,
[FTRem] Between 52 And 57,0.9,
[FTRem] Between 58 And 60,1) AS FltTimeSwitch
FROM tblFlights;



Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Guest]

The following is adapted from a function of time for returning the duration
between two times in the format hh:nn. As amended it takes the take-off and
landing times as integers e.g. 2142:

Public Function FlightTime( _
intFrom As Integer, _
IntTo As Integer) As String

Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutes As String
Dim strDaysHours As String
Dim dblDuration As Double
Dim dtmFrom As Date, dtmTo As Date

dtmFrom = CDate(Format(intFrom, "00:00"))
dtmTo = CDate(Format(IntTo, "00:00"))

' if dtmFrom is later than dtmTo then
' move dtmTo to following day
If dtmFrom > dtmTo Then dtmTo = dtmTo + 1

dblDuration = dtmTo - dtmFrom

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutes = Format(dblDuration, ":nn")

FlightTime = lngHours & strMinutes

End Function


The following is an amendment to return the duration in hours to one decimal
place:


Public Function FlightHours( _
intFrom As Integer, _
IntTo As Integer) As Single

Const HOURSINDAY = 24
Dim dblDuration As Double
Dim dtmFrom As Date, dtmTo As Date

' convert integer values to date/time values
dtmFrom = CDate(Format(intFrom, "00:00"))
dtmTo = CDate(Format(IntTo, "00:00"))

' if dtmFrom is later than dtmTo then
' move dtmTo to following day
If dtmFrom > dtmTo Then dtmTo = dtmTo + 1

dblDuration = dtmTo - dtmFrom

'get number of hours
FlightHours = Round(dblDuration * HOURSINDAY, 1)

End Function

You'd call then both by passing the take-of and landing time into the
function:

FlightTime([TakeOffTime], [LandingTime])

or:

FlightTime([TakeOffTime], [LandingTime])

Both assume that no flight can be more than 23 hours and 59 minutes.

Note that the rounding by the Round function does not quite agree with your
conversion table, so if you wish to return your values you'd need to use the
first function and look up the corresponding value in hours to one decimal
place from your conversion table.

Ken Sheridan
Stafford, England