Time formatting issues

[C4X]

I am nearly finished working on my latest project, a data entry GUI in
VBA (2003), and have run into difficulties dealing with time values.

Brief project background: It is a simple data entry form which takes
various bits of flight data and enters them onto a spreadsheet. The
key entries I'm having trouble with are 'Time' values. Two fields are
automatically populated with the appropriate 'Departure' and 'Arrival'
times based on a 'ListIndex' run during initialization, which allows
values for the 'Flight Numbers'. An 'If...Then' statement refers back
to the 'ListIndex':

private sub fltNum_change
......
If fltNum.ListIndex = 3 then 'Flight Number
fltFrom.value="nan" 'Departure airport
fltTo.value="suv" 'Arrival airport
fltATD.value="5:10" 'Departure time
fltATA.value="5:40" 'Arrival time
end if
......
end sub

To allow the user to incrementally change the values, there are spin-
buttons with the following code that formats a time value:

private sub spinbutton3_SpinDown()
fltATD.text=format(timevalue(fltATD.text) - timevalue("00:01"),
"h:mm")
end sub

private sub spinbutton3_SpinUp()
fltATD.text=format(timevalue(fltATD.text) + timevalue("00:01"),
"h:mm")
end sub

This is where I lose the path

My next two (and final) goals elude me:
1) Display a value (value 'A') which is '3 minutes' less than the
'departure time' in a textbox with spin-button control, as well as a
value (value 'B') '3 minutes' more than the 'arrival time' in yet
another textbox with spin-button control.

2) Show a value which is value 'B' minus value 'A' in a textbox on the
userform in decimal-time format. I have the 4 values currently output
to a spreadsheet, and am able to easily subtract 'B' from 'A' and show
in a cell formatted with '=(E1-INT(E1))*24'. The correct decimal time
is shown, but I cant manage to show the value on my userform (and
subsequently allow the data to be entered onto the proper
spreadsheet).

Help please! If I can sort these two hurdles out, my project is
done.
Thnx
Clarke

 

[Rob van Gelder]

I'm a little confused, because you've already supplied most of the answer for goal 1.

In any case, this is probably the code you want.

TextBoxA.Text = Format(TimeValue(fltATD.Text) - TimeValue("00:03"), "h:mm")
TextBoxB.Text = Format(TimeValue(fltATA.Text) + TimeValue("00:03"), "h:mm")
TextBoxC.Text = Format(TimeValue(TextBoxA.Text) - TimeValue(TextBoxB.Text), "h:mm")

I see a problem developing when the departure/arrival times are within 3 minutes of midnight.
You might want to check for these special cases.

Cheers,
Rob

 

[C4X]

I'm a little confused, because you've already supplied most of the answerfor goal 1.

In any case, this is probably the code you want.

     TextBoxA.Text = Format(TimeValue(fltATD.Text) - TimeValue("00:03"), "h:mm")
     TextBoxB.Text = Format(TimeValue(fltATA.Text) + TimeValue("00:03"), "h:mm")
     TextBoxC.Text = Format(TimeValue(TextBoxA.Text) - TimeValue(TextBoxB.Text), "h:mm")

I see a problem developing when the departure/arrival times are within 3 minutes of midnight.
You might want to check for these special cases.

Cheers,
Rob

Okay, thnx Rob. I have been staring at this off and on for a while
now. It gets blurry Should have worked that out. Anyways, we have
no flights around midnight, should be no prob there. Any thoughts on
how to format the total flight time as a decimal time (i.e: h.mm)? I
haven't come across it and the 'cell formula' I posted above is
unusable it seems . . .thnx.

 

[C4X]

Okay, thnx Rob. I have been staring at this off and on for a while
now. It gets blurry Should have worked that out. Anyways, we have
no flights around midnight, should be no prob there. Any thoughts on
how to format the total flight time as a decimal time (i.e: h.mm)? I
haven't come across it and the 'cell formula' I posted above is
unusable it seems . . .thnx.

Disregard, I missed it. Thnx again!