我已经使用此自定义类型格式化了 Excel 2010 中的一些单元格:
##":"##
因为我希望用户能够输入“1345”来获得“13:45”或“923”来获得“9:23”。这毫无困难。
我的问题是,在某些情况下,用户可能只输入“13”,这会产生一些奇怪的结果,例如“-1”。
是否可以格式化单元格,以便将“13”格式化为“13:00”,同时仍保持上面提到的格式?
谢谢。
答案1
我无法重现“13”>“-1”场景。您的区域设置是什么?
但您的自定义格式肯定不能处理午夜至凌晨 1 点之间的时间。013 中的前导零将被删除。
问题在于,您不知道输入 13 的用户指的是 00:13 还是 13:00。只要这有待解释,任何格式、公式或代码解决方案都无济于事。
这更多的是关于用户教育,而不是其他任何事情。
您可能需要应用 VBA 而不是自定义格式来确保
a)用户输入足够的字符,以便毫无疑问地解释时间值,例如 013 表示 0:13,1300 表示 13:00
b)这些值不仅仅是格式化为看起来像时间,而且实际上是可以用于计算时间差的时间值
c) 输入时间时,前导零不会被删除。
下面是将此类条目转换为日期/时间值的 UDF。它还包含通过在条目中添加一个或多个 + 或 - 符号来增加或减少天数的功能。从工作表更改事件中调用此函数。
Public Function TimeEntry(iTarget As String) As Variant
' convert values into date/times
'
' expected user input in the format of
'
' 1430 will be converted to today, 14:30
' 1430+ will be converted to today + 1 day, 14:30
' 1430- will be converted to today - 1 day, 14:30
'
' multiple + or - signs are allowed to enable quick entry of time several days ago or
' in the future
'
Dim IncDay As Integer, DecDay As Integer
Dim eTime As Variant
On Error GoTo Message
Msg = ""
eTime = Application.WorksheetFunction.Substitute(iTarget, "+", "")
eTime = Application.WorksheetFunction.Substitute(eTime, "-", "")
eTime = Format(eTime, "0000")
' a few error checks to validate the data
' - can only start with a number
' - must be a number after stripping off the + and - signs
' - cannot be less than 3 or more than 4 digits
' - cannot be more than 23:59
If Not IsNumeric(Left(iTarget, 1)) Or _
Not IsNumeric(eTime) Or _
Len(eTime) > 4 Or _
eTime > 2359 Then
GoTo Message
End If
' insert a colon before the last two digits and convert into a time
eTime = Left(eTime, Len(eTime) - 2) & ":" & Right(eTime, 2)
eTime = TimeValue(eTime)
' determine how many days to increase or decrease
IncDay = Len(iTarget) - Len(Application.WorksheetFunction.Substitute(iTarget, "+", ""))
DecDay = Len(iTarget) - Len(Application.WorksheetFunction.Substitute(iTarget, "-", ""))
' increase/decrease current date and add the time value
TimeEntry = Date + IncDay + (DecDay * -1) + eTime
GoTo Ende
Message:
Msg = "Invalid time value entered" & Chr(10) & Chr(10)
Msg = Msg & "Please enter time values like this: " & Chr(10) & Chr(10)
Msg = Msg & " 900 for 9:00 am today " & Chr(10)
Msg = Msg & "2130+ for 21:30 tomorrow " & Chr(10)
Msg = Msg & " 000+ for midnight tonight" & Chr(10)
Msg = Msg & "1000-- for 10 am two days ago."
MsgBox Msg
TimeEntry = ""
Ende:
End Function