CODEWITHSUNDEEP
excelvbscript
Arup Rakshit
Arup Rakshit

Reputation: 118261

Date value subtraction producing wrong error

I am using the below code to show the date difference in Day:Hour:Minute format.

 Function TimeSpan(dt1, dt2) 

    Dim seconds,minutes,hours,days

    If (isDate(dt1) And IsDate(dt2)) = false Then 
        TimeSpan = "00:00:00" 
        Exit Function 
    End If 

    seconds = Abs(DateDiff("S", dt1, dt2)) 
    minutes = seconds \ 60 
    hours = minutes \ 60 
    days  = hours \ 24
    minutes = minutes mod 60 
    seconds = seconds mod 60 
    days    = days    mod 24 

    if len(hours) = 1 then hours = "0" & hours 

    TimeSpan = days& ":" & _ 
        RIGHT("00" & hours , 2) & ":" & _ 
        RIGHT("00" & minutes, 2) 
End Function

But it is not producing expected values for some cases.

  D1=#9/24/2012  8:09:15 AM# and D2=#9/25/2012  8:09:15 AM# gives correct data like 1:24:00 whereas below are producing error when working with VBScript and Excel.
  D1=#9/5/2012  8:45:43 AM# and D2=#9/25/2012  8:45:43 AM# result=0.888888888888889
  D1=#9/6/2012  8:29:34 AM# and D2=#9/17/2012  8:59:36 AM# result=0.503125

Can you explain why so?

Thanks

Upvotes: 0

Views: 507

Answers (2)

bonCodigo
bonCodigo

Reputation: 14361

Try my answer from an earlier post in your UDF as the following: This answer is in VBA

Please declare all variables and force yourself to declare by adding option explicit :)

option explicit
Function TimeSpan(dt1 As Date, dt2 As Date) As String
Dim dtTemp As Date

    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 ;)
        TimeSpan = Application.WorksheetFunction.Text((dt2 - dt1), "dd:hh:mm:ss")

    Application.ScreenUpdating = False
End Function

UDF Output:

enter image description here

But I really suggest you to use Excel sheet functions if you have the freedom and possibility to do so.

If date difference is more than 31 days

Then use the solution as per this article Incorporate the DateDiff to the UDF.

Upvotes: 1

K_B
K_B

Reputation: 3678

Be aware I'm more used to write VBA, so you might need to tweak here and there.

Alternatively you could just subtract the two dates from eachother as numerical value:

Dim dblDateDiff as Double
dblDateDiff = Abs(dt2 - dt1)

Now the Timespan would be (dont use "d" as that would not include months and years that could have passed):

Timespan = Int(dblDateDiff) & ":" & Hour(dblDateDiff) & ":" & Minute(dblDateDiff)

If the direction (positive or negative) of the Timespan is relevant you could change the last line into:

Timespan = Sgn(dblDateDiff) * Int(dblDateDiff) & ":" & Hour(dblDateDiff) & ":" & Minute(dblDateDiff)

For your time formatting issue either:

  1. Set the number format of the output cell to Text, or

  2. Add a single quote in front of the rest of the string:

     Timespan = "'" & Sgn(dblDateDiff) * Int(dblDateDiff) & ":" & Hour(dblDateDiff) & ":" & Minute(dblDateDiff)

Upvotes: 1

Related Questions