使用格式函数时"MM“站立数月与"mm”站立数分钟的区别

Question

我的问题是，自从上一次工作事故发生以来，我一直在努力确定滚动计数。下面的代码以一种方式工作。滚动计数工作正常，但是从月份和分钟的单词(小时、分钟、秒等)中删除"s“有一个问题。

这是我的密码：

Sub LTI_Sub()

Static etime

'Exit Sub ' Uncomment to Stop

etime = Now() + TimeSerial(0, 0, 1)

Sheets("LTI").Range("Time") = LTI(Sheets("LTI").Range("Last"))

Application.OnTime etime, "LTI_Sub", , True


End Sub


Function LTI(LastLTI As Date)

x = Now() - LastLTI

Yx = Format(x, "yy")
If Yx = 1 Then
YS = ""
Else
YS = "s"
End If

Mx = Format(x, "mm")
If Mx = 1 Then
MS = ""
Else
MS = "s"
End If

Dx = Format(x, "DD")
If Dx = 1 Then
Ds = ""
Else
Ds = "s"
End If

Hx = Format(x, "HH")
If Hx = 1 Then
Hs = ""
Else
Hs = "s"
End If

MMx = Format(x, "MM")
If MMx = 1 Then
MMs = ""
Else
MMs = "s"
End If

Sx = Format(x, "SS")
If Sx = 1 Then
Ss = ""
Else
Ss = "s"
End If

LTI = Format(x, "YY \Y\e\a\r\" & YS & ", mm \M\o\n\t\h\" & MS & ", DD \D\a\y\" & Ds & "," & vbNewLine & "HH \H\o\u\r\" & Hs & ", MM \M\i\n\u\t\e\" & MMs & ", \A\n\d SS \S\e\c\o\n\d\" & Ss)

End Function

现在，我不知道VBA在实际格式化时间时如何知道mm和MM之间的区别，但是在确定Mx和MMx是否需要"s“的行中，它总是将其视为一个月值。我该怎么告诉你几分钟？

x = Now() - LastLTI行也有一个奇怪的“错误”( LastLTI是上一次事故的日期)。当在VBA中返回时，它会返回额外的一个月和一天，但在Excel中返回正确的值。例如，如果从lat事故(一直到第二天)已经整整一天了，VBA将返回以下字符串："00岁，01个月，02天，00小时，00分，00秒“<--注意，由于”月份“等于1，所以分钟已经删除了S。

我希望这能解释我想要达到的目标！

提前感谢

Answer 1

我使用了一些不同的日期函数，包括DateDiff，它返回给定给指定时间间隔的两个日期之间的差异，而DateAdd则通过允许您将指定的间隔添加到日期值中来实现与此相反的操作。我还使用了TimeValue函数，它只返回日期的时间部分。

我认为这得到了你想要的，或者至少应该让你非常接近。

Function LTI(LastLTI As Date)
Dim yx As Long
Dim mx As Long
Dim dx As Long
Dim hx As Long
Dim mmx As Long
Dim sx As Long
Dim ys As String
Dim ms As String
Dim ds As String
Dim hs As String
Dim mms As String
Dim ss As String

Dim dtNow As Date
dtNow = Now()

yx = DateDiff("yyyy", dtNow, LastLTI)
ys = IIf(yx = 1, "", "s")

mx = DateDiff("m", DateAdd("yyyy", yx, dtNow), LastLTI)
ms = IIf(mx = 1, "", "s")

dx = Format(dtNow - LastLTI, "dd")
ds = IIf(dx = 1, "", "s")

hx = DateDiff("h", TimeValue(dtNow), TimeValue(LastLTI))
hs = IIf(hx = 1, "", "s")

'compute the remaining minutes not allocated to a whole hour, above:
mmx = Format(TimeValue(dtNow), "n") - Format(TimeValue(LastLTI), "n")
mms = IIf(mmx = 1, "", "s")

' compute the remaining seconds not allocated to a whole minute, above:
sx = Format(TimeValue(dtNow), "ss") - Format(TimeValue(LastLTI), "ss")
ss = IIf(sx = 1, "", "s")

LTI = yx & "\Y\e\a\r\" & ys & ", " & _
        mx & "\M\o\n\t\h\" & ms & ", " & _
        dx & "\D\a\y\" & ds & "," & vbNewLine & _
        hx & "\H\o\u\r\" & hs & ", " & _
        mmx & "\M\i\n\u\t\e\" & mms & ", \A\n\d " & _
        sx & "\S\e\c\o\n\d\" & ss

End Function

Answer 2

与其使用Format (expression, "mm")几分钟，不如尝试Format (expression, "n")。