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

That looks very familiar to this post ;) So just tell us what your sheet is doing over all. Since you have many other scripts running and screwing your sheet. A programme has to be compatible and have a proper data flow. Please share with us what you are trying to do here, not pieces here and there. :) — bonCodigo
– bonCodigo, Commented Dec 16, 2012 at 13:41
See, when the above function calculating the time duration,they are perfect.But when i was moving the cell values from right to left is blank space there,it data formatting got lost. that is the problem. — arun_roy
– arun_roy, Commented Dec 16, 2012 at 13:52
What does your sheet do? How does it look like? What do you mean by blank space? Is it a blank space or a blank cell? — bonCodigo
– bonCodigo, Commented Dec 16, 2012 at 14:28
Sorry Blank cell.Okay I am telling you one thing that,after the above function's time duration calculation it gets stored in an specific column with the format "dd:hh:mm",but in the formula bar of the excel sheet it is still showing AM or PM? it hasn't been removed from the duration as its post component. — arun_roy
– arun_roy, Commented Dec 16, 2012 at 14:32
and when the scripting producing such data,there are scattered within each row,so another script made them contiguous,and at that time such date formating got lost again that issue "AM or PM"... — arun_roy
– arun_roy, Commented Dec 16, 2012 at 14:35

LarryF · Accepted Answer · 2014-11-07 23:51:55Z

0

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.

edited Nov 7, 2014 at 23:51

answered Nov 7, 2014 at 23:30

LarryF

5,1334 gold badges35 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Date - time format issue in excel using VBScript

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related