0

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
Community
  • 1
  • 1
arun_roy
  • 601
  • 6
  • 16
  • 42
  • 1
    That looks very familiar to this [post](http://stackoverflow.com/questions/13837023/date-value-subtraction-producing-wrong-error) ;) **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 Dec 16 '12 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 Dec 16 '12 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 Dec 16 '12 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 Dec 16 '12 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 Dec 16 '12 at 14:35
  • After each script runs, have one line of code to make sure all your date time cell formatting gets back in your desired format without `AM, PM` – bonCodigo Dec 16 '12 at 15:48

1 Answers1

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.

LarryF
  • 4,925
  • 4
  • 32
  • 40