日期-时间格式问题在Excel中使用VBScript [英] Date - time format issue in excel using VBScript

问题描述

以下代码用于计算两个日期之间的持续时间，例如" 11/13/2012 11:21:41 AM "和" 11/14/2012 2:下午32:59 ".在这种情况下，该函数将产生正确的输出: 1:03:11 (采用dd:hh:mm格式，因此是1天，3小时和11分钟).计算后，脚本会将这个值存储到一个Cell中，到目前为止，它仍然是完美的，并且正是我想要的格式.此后，另一个脚本将根据需要和脚本中的某些逻辑运行并移动该单元格的值，并将其动态存储到所需的单元格中.我遇到的问题是，持续时间正在向该值添加"AM"或"PM"，这是不正确的，因为这是一个非常特定的(并且是格式化的)持续时间，而不是一天中的实际时间，该Excel似乎被视为TIME值.

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 

推荐答案

查看Excel中的 FormatDateTime()函数.这可能有助于您仅将所需的内容存储在单元中.其次，当我使用Excel进行大量的日期格式化时，我确保单元格包含我希望放入单元格中的数据类型的FORMAT信息.这可能对您有所不同.在我看来，Excel可能会将您的值视为TIME值，可能是在常规"单元格格式设置规则下.设置将数据放入其中的单元格，以使用特定的"STRING"格式.这样，Excel不会对您的数据做任何假设，然后您可以按照自己认为合适的方式对其进行解析.

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.

这篇关于日期-时间格式问题在Excel中使用VBScript的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！