Dim HourPart As Integer
Dim MinutePart As Integer
If Not Intersect(prevTarget, Range("E8:F52")) Is Nothing Then 'If it's in the time area of the worksheet
'This is to skip the formatting code if the entry is blank or has a formula
If prevTarget.Value = 0 Or prevTarget.Value = "" Then
Exit Sub
End If
If prevTarget.Value >= 0 And prevTarget.Value < 1 Then 'This is to skip the formatting code if the entry already has a colon (If it already has a colon, then Excel will automatically format it for time and give it a value less than 1)
If Len(prevTarget) = 4 Then 'This If is to define the minutes to make sure not over 60
MinutePart = Mid(prevTarget, 3, 2)
ElseIf Len(prevTarget) = 5 Then
MinutePart = Mid(prevTarget, 4, 2)
End If
If MinutePart >= 60 Then
MsgBox "Minutes shouldn't be greater than or equal to 60"
End If
GoTo ChangeAmPm
ElseIf prevTarget.Value > 100 And prevTarget.Value < 2359 Then 'This is to handle hour and minute time values that are entered without a colon
HourPart = prevTarget.Value \ 100
MinutePart = prevTarget - HourPart * 100
If MinutePart >= 60 Then
MsgBox "Minutes shouldn't be greater than or equal to 60"
End If
prevTarget = HourPart & ":" & MinutePart
ElseIf prevTarget.Value >= 1 And prevTarget.Value <= 12 Then 'This is to handle time code where only an hour is entered and without a colon
HourPart = prevTarget.Value
MinutePart = 0
prevTarget = HourPart & ":"
End If
End If
1条答案
按热度按时间cx6n0qe31#
对于任何感兴趣的人,这是我想出的快速时间进入: