CODEWITHSUNDEEP
excelvbscriptvba
Arup Rakshit
Arup Rakshit

Reputation: 118261

Date time error in VBScript

My below code is doing kind of duration calculation, but at run time it is throwing an exception that type mismatch, although both of the parameter has date values within them.So any suggestion where the bug is?

Code:

Function TimeSpan(dt1,dt2)
Dim dtTemp

objExcel1.Application.ScreenUpdating = False
    If (IsDate(dt1) And IsDate(dt2)) = False Then
        TimeSpan = "00:00:00"
        Exit Function
    End If

    If dt2 < dt1 Then
        dtTemp = dt2
        dt2 = dt1
        dt1 = dt2
    End If
    '-- since you only had days, I have put up to days here. 
    '-- if you require months, years you may use yy:mm:dd:hh:mm:ss
    '-- which is pretty self-explainatory ;)
    Msgbox("DT2:" & dt2 & "DT1:" & dt1)
    TimeSpan = objExcel1.Application.WorksheetFunction.Text((dt2 - dt1), "[h]:mm:ss")'"dd:hh:mm:ss"

objExcel1.Application.ScreenUpdating = True
End Function

Date values to the parameters passed by the Calle

Date error

@Tomalak I have updated the code as per you and now getting below error.

CODE

Function TimeSpan(dt1, dt2)
    If Not (IsDate(dt1) And IsDate(dt2)) Then

        TimeSpan = "00:00:00"

    ElseIf dt2 < dt1 Then

        TimeSpan = TimeSpan(dt2, dt1)

    Else

        MsgBox((CDate(dt1) - CDate(dt2)))
        TimeSpan = objExcel1.Application.WorksheetFunction.Text( (CDate(dt1) - CDate(dt2)), "[h]:mm:ss" )

    End If  

End Function

Msgbox Displaying:

enter image description here

New Error

Upvotes: 0

Views: 539

Answers (2)

phoog
phoog

Reputation: 43046

This is just a guess, but your values might be string values. IsDate() will return true if the value is a string that can be converted to a date. But, in that case, the subtraction will fail. You can fix that with the CDate() or CVDate() function:

TimeSpan = objExcel1.Application.WorksheetFunction.Text((CDate(dt2) - CDate(dt1)), "[h]:mm:ss")'"dd:hh:mm:ss"

To work around the "unable to get..." error, try this:

Dim bSign
bSign = CDate(dt2) < CDate(dt1)
TimeSpan = objExcel1.Application.WorksheetFunction.Text(Abs(CDate(dt2) - CDate(dt1)), "[h]:mm:ss")'"dd:hh:mm:ss"
If bSign Then
    TimeSpan = "-" & TimeSpan
End If

Upvotes: 2

J&#252;ri Ruut
J&#252;ri Ruut

Reputation: 2530

There is a function DateDiff() available, which makes life simpler:

 Function TimeSpan(dt1, dt2)

    If Not (IsDate(dt1) And IsDate(dt2)) Then
        TimeSpan = "00:00:00"
    Else
       TimeSpan = Abs(DateDiff("s", dt2, dt1)) / 3600
         TimeSpan = TimeSpan / 24
    End If

 MsgBox (TimeSpan)

The result is in days, re-calculated from seconds to achieve the needed (minutes and seconds) accuracy and compatibility with date formats.

Upvotes: 2

Related Questions