Date - time format issue in excel using VBScript

Question

The following code is used to calculate the duration between two dates, for example "11/13/2012 11:21:41 AM" and "11/14/2012 2:32:59 PM". The function is producing the correct output, in this case: 1:03:11 (in dd:hh:mm format, so 1 day, 3 hours, and 11 minutes). After calculation the script stores this value into a Cell, which up until now is perfect, and exactly the format I want. After that, another script runs and moves that cells value depending on the needs and some logic in the script, and stores it to the required cell dynamically. The problem I'm having is, the duration is getting "AM" or "PM" added to the value, which is incorrect, since this is a very specific (and FORMATTED) DURATION, and NOT an actual time of day, which Excel seems to be treating as a TIME value instead.

Here is the code used to measure the durations:

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

objExcel1.Application.ScreenUpdating = False
End 

Answer 1

Look into the FormatDateTime() function in Excel. This might help you store only what you intend in the Cells. Secondly, when I was working with Excel, doing a lot of date formatting, I made sure that the cells contained FORMAT information for the type of data I expected to put in the cell. This might make the difference for you. It seems to me that Excel is treating your value as a TIME value, possibly under the "General" cell formatting rule. Set your cells that you put this data into, to use perhaps, a specific "STRING" format. This way, Excel will not make any assumptions about your data, and then you can parse it anyway you see fit.