date/time compare and then perform an action

Question

when one of my staff travel, they are entitled to travel comptime. My access vba already compares the date/time of departure to date/time of arrival at work site. How can I subtract the work hours from the flight time? to make matters even crazier, I have to account for time zones.

here the example I am working with (because if I an get this, the rest will fall into line). staff departs Manila Philippines (UTC 8) on 3/7/22 at 00:15, arrives Washington DC (UTC -5) 3/7/22 at 16:10. total flight time is 1735 minutes (28 hours 55 minutes). Since the flight was over the workday, I need to subtract 480 minutes from the flight time.

How can I code this to date/time compare the workday and the flight date/time depart and date/time arrive to subtract out the 480 minutes of the workday?

I know the code i have attached may have "air code", but I am not a programmer by trade, just a guy trying to help his staff earn the most comptime they can.

depflt = MsgBox("Was day of departure a workday?", vbQuestion + vbYesNo)

    If depflt = vbYes Then
        If DTDeptdy < Strworkday Then
            Me.TxtholdtimeDiff = DateRound(DTDeptdy - Strworkday, 0, 15, 0)
            Me.TxtholdtimeDiff = Format(Me.TxtholdtimeDiff, "h") * 60 + Format(Me.TxtholdtimeDiff, "n")
            pda = Me.TxtholdtimeDiff
            'Me.TxtHoldTrvAirport = DateRound(arvairport - gotoairport, 0, 15, 0)
            'Me.TxtHoldTrvAirport = Format(Me.TxtHoldTrvAirport, "h") * 60 + Format(Me.TxtHoldTrvAirport, "n")
            Trvdiff = Me.TxtHoldTrvAirport
            
        Else
            If DTDeptdy > Strworkday And DTDeptdy < Endworkday Then
                pda = 0
            Else
                If DTDeptdy > Endworkday Then
                    Me.TxtholdtimeDiff = DateRound(DTDeptdy - Endworkday, 0, 15, 0)
                    Me.TxtholdtimeDiff = Format(Me.TxtholdtimeDiff, "h") * 60 + Format(Me.TxtholdtimeDiff, "n")
                    pda = Me.TxtholdtimeDiff
                    Me.TxtHoldTrvAirport = DateRound(arvairport - gotoairport, 0, 15, 0)
                    Me.TxtHoldTrvAirport = Format(Me.TxtHoldTrvAirport, "h") * 60 + Format(Me.TxtHoldTrvAirport, "n")
                    Trvdiff = Me.TxtHoldTrvAirport
                        If pda >= 180 Then
                            pda = 180
                        End If
                End If
            End If
        End If
Else
    Me.TxtHoldTrvAirport = DateRound(arvairport - gotoairport, 0, 15, 0)
    Me.TxtHoldTrvAirport = Format(Me.TxtHoldTrvAirport, "h") * 60 + Format(Me.TxtHoldTrvAirport, "n")
    Trvdiff = Me.TxtHoldTrvAirport
    pda = 180
End If
    
'sets variable to arrival date/time of flight
DTArvtdy = DateValue(Me.txtDateArvTDY) + TimeValue(Me.txtTimeFltArv)

    If txtDateArvTDY = txtDateDepTDY And DTArvtdy < Strworkday Or DTArvtdy > endoworkday Then
         arvtime = Me.txtArvAllowance * 60
    Else
         arvtime = 0
    End If
    
' determine the number of hours between date depart tdy and date arrive tdy
Me!txtHoldTime = DateRound(DTArvtdy - DTDeptdy, 0, 15, 0)
Me!txtHoldTime = Format(Me.txtHoldTime, "h") * 60 + Format(Me.txtHoldTime, "n")

' determine time diff between date arrived tdy and date depart tdy
TimeDiff = DateDiff("d", DTDeptdy, DTArvtdy)
TimeDiff = TimeDiff * 24 * 60

' determine time zone value if TimeDiff >=1
If TimeDiff <> 1 Then
tzvalue = TxtDutyStationUTC.Value - TxtTDYLocUTC.Value
    
    If tzvalue >= 1 Then
        tzvalue = tzvalue * 60
    Else
        tzvalue = -tzvalue * 60
    End If
End If
'sums the total time span
totmindep = arvtime + pda + tzvalue + Me.txtHoldTime + Trvdiff + TimeDiff + pdaDep

    If totmindep < 0 Then
        totmindep = 0
    Else
        totmindep = totmindep - workdaymin
    End If

'determines the actually allowable travel comptime.
'totalCTDep = Format(totmindep \ 60, "0") & ":" & Format(totmindep Mod 60, "00")

' sets the textbox to the total allowable travel time
' txtCTHADOD.SetFocus
' txtCTHADOD.Text = totalCTDep

' holds the total time on the outward leg of the journey for use later in the program
mytempvar = totmindep

' used to store total CompTime hours earned departing on TDY
Me.TxtHoldHoursDep = mytempvar / 60

Answer 1

I used this code to check if the dates are more than 24 hours apart:

// determine time diff between date arrived tdy and date depart tdy
TimeDiff = DateDiff("d", DTDeptdy, DTArvtdy)
TimeDiff = TimeDiff * 24 * 60
    If depflt = vbYes And TimeDiff = 1 Then
        noCTforworkday = -480
    End If