Option Explicit
Private Sub Main()
Debug.Print """"; Format$(#1:30:20 AM#, "\[Hh, Nn, Ss]"); """"
Debug.Print """"; Format$(#12:00:20 PM#, "\[Hh, Nn, Ss]"); """"
End Sub
Code:
"[01, 30, 20]"
"[12, 00, 20]"
If yes then see the documentation of the Format$ function and its See Also link.
If you want to extract the hour, minute and second component of a time value instead, then see the Hour, Minute and Second functions.
EDIT
You might also want to check out the DateDiff function.
Last edited by Bonnie West; Aug 23rd, 2015 at 02:51 PM.
On Local Error Resume Next: If Not Empty Is Nothing Then Do While Null: ReDim i(True To False) As Currency: Loop: Else Debug.Assert CCur(CLng(CInt(CBool(False Imp True Xor False Eqv True)))): Stop: On Local Error GoTo 0
Private Sub Command1_Click()
Dim Entering As Double
Dim Leaving As Double
Dim Present As Date
Entering = CDbl(#1:30:20 AM#)
Leaving = CDbl(#12:00:20 PM#)
Present = CDate(Leaving - Entering)
Print Present
End Sub
do not put off till tomorrow what you can put off forever
Simple subtraction or addition with Date typed data isn't really a defined operation, though VB will let you get away with it. However you can end up with garbage results pretty easily as soon as you move across a day boundary or have a negative result.
This is less of a problem if you absolutely know you have no negative differences or differences of 24 hours or more, but even then it probably isn't a good practice.
Getting it right can be tricky, and I might still have "negative flaws" here but it looks good.
Code:
Option Explicit
Private Function TDiff(ByVal Later As Date, ByVal Earlier As Date) As Double
TDiff = CDbl(Later) - CDbl(Earlier)
End Function
Private Function FormatTDiff(ByVal TDiff As Double) As String
Dim TDiffSgn As Integer
TDiffSgn = Sgn(TDiff)
TDiff = Abs(TDiff)
If TDiffSgn < 0 Then FormatTDiff = "-"
If TDiff >= 1# Then
FormatTDiff = FormatTDiff & CStr(Int(TDiff)) & "d, "
TDiff = TDiff - Int(TDiff)
End If
FormatTDiff = FormatTDiff & Format$(CDate(TDiff), "Hh:Nn:Ss")
End Function
Private Sub cmdDifference_Click()
lblDiff.Caption = FormatTDiff(TDiff(dtpDOut.Value + dtpTOut.Value, _
dtpDIn.Value + dtpTIn.Value))
End Sub
Check it over though, I still feel uneasy about the negative differences.
Simple subtraction or addition with Date typed data isn't really a defined operation, though VB will let you get away with it. However you can end up with garbage results pretty easily as soon as you move across a day boundary or have a negative result.
Care to give some examples?
Keeping transitivity of the (positively) defined Date + Double operation we have Date(x) +/- Double(y) = Date(z) => Date(x) +/- Date(z) = Double(y)
CDate(-2.5) is a valid Date too.
@GywGod133: I'm using this helper function for duration output: FormatDuration(ClockOut-ClockIn)
Code:
Public Function FormatDuration(ByVal dDate As Date) As String
Dim lHours As Long
lHours = DateDiff("h", 0, dDate)
FormatDuration = Format$(lHours, "0") & Mid$(FormatTime(DateAdd("h", -lHours, dDate)), 3)
End Function
Public Function FormatTime(ByVal dDate As Date) As String
FormatTime = Format$(dDate, IIf(Second(dDate) <> 0, "hh:mm:ss", "hh:mm"))
End Function
Keep in mind that duration can get above 24 hours so you cannot use std Format function on a Date variable as is.
cheers,
</wqw>
Last edited by wqweto; Aug 25th, 2015 at 03:29 AM.
I was referring to the difference in results you see when conventional formatting is applied. Everything might seem fine as long as you have positive values less than 24 hours, but then it falls apart.
Code:
Dim D As Date
D = #1/1/2000 12:00:00 PM# - #1/2/2000 11:00:00 AM#
Debug.Print "Looks ok: "; Format$(D, "Hh:Nn:Ss")
D = #1/2/2000 12:00:00 PM# - #1/1/2000 12:00:00 PM#
Debug.Print "Looks wrong: "; Format$(D, "Hh:Nn:Ss")
D = #1/1/2000 12:00:00 PM# - #1/2/2000 1:00:00 PM#
Debug.Print "Looks wrong: "; Format$(D, "Hh:Nn:Ss")
Looks ok: 23:00:00
Looks wrong: 00:00:00
Looks wrong: 01:00:00
I see, things get wrong real quick. Btw, I noticed that Debug.Print TypeName(Abs(CDate(-2.5))) returns Date which is news for me that Abs has built-in support for Date datatype.
I frankly never intended FormatDuration to be used with "negative" durations and I'm bit surprised it works ok as is with your input
In the end all we can conclude is that Date type isn't meant for intervals. An alternative might be to just use DateDiff("s", d1, d2), work with the Long seconds value, and write a formatting function to create a display format String when required.
Ok, so DateDiff failed me badly. Here is a "manual" calculation of hours difference
Code:
Public Function FormatDuration(ByVal dDate As Date) As String
Dim lHours As Long
' lHours = DateDiff("h", 0, dDate)
lHours = Round(dDate * 24& * 60 * 60) \ (60& * 60)
FormatDuration = Format$(lHours, "0") & Mid$(FormatTime(DateAdd("h", -lHours, dDate)), 3)
End Function