英文:
Change number format to remove parentheses
问题
我有一个简短的UDF，用于创建一个时间标记。它读取的数据格式如下："03/11/2004 16:00:00"。UDF仅在没有括号时有效，即："03/11/2004 16:00:00"，那么如何让它移除括号（而不覆盖源数据）或更改数字格式以符合我的需求？
Function TimeMark(xtime, delay)
Dim H As Integer, M As Integer, S As Integer, D As Integer, Mon As Integer, Y As Integer
xtime = xtime + delay / 24
S = 0
M = 0
H = Hour(xtime)
D = Day(xtime)
Mon = Month(xtime)
Y = Year(xtime)
TimeMark = TimeSerial(H, M, S) + DateSerial(Y, Mon, D)
End Function
英文:
I have a short UDF which creates a time mark. The data it reads is in the following format: "(03/11/2004 16:00:00)".
The UDF only works without the parentheses i.e. "03/11/2004 16:00:00" so how can I get it to remove the parentheses (without overwriting the source data) or change the number format to what i need?
Function TimeMark(xtime, delay)
Dim H As Integer, M As Integer, S As Integer, D As Integer, Mon As Integer, Y As Integer
xtime = xtime + delay / 24
S = 0
M = 0
H = Hour(xtime)
D = Day(xtime)
Mon = Month(xtime)
Y = Year(xtime)
TimeMark = TimeSerial(H, M, S) + DateSerial(Y, Mon, D)
End Function
答案1
得分: 1
将@CHill60和@ToddlesonA的输入合并，你就得到了一行代码：
Public Function TimeMark(ByVal xtime As String, ByVal delay As Double) As Date
TimeMark = CDate(Replace(Replace(xtime, "(", ""), ")", "")) + delay / 24
End Function
英文:
Combine input from @CHill60 and @ToddlesonA and you have a one-liner:
Public Function TimeMark(ByVal xtime As String, ByVal delay As Double) As Date
TimeMark = CDate(Replace(Replace(xtime, "(", ""), ")", "")) + delay / 24
End Function
答案2
得分: 0
使用 Mid() 函数来移除括号，使用 DateValue 函数来提取日期数值。
' 此函数接受一个表示时间的字符串和一个延迟（以小时为单位），
' 并返回一个时间标记，即原始时间加上延迟。
Function TimeMark(xtime As String, delay As Double)
Dim Result As Date ' 存储结果日期
' 通过移除字符串的第一个和最后一个字符来提取时间，
' 并将其转换为日期/时间类型。这里的假设是 xtime
' 包含在附加字符中的时间字符串（例如，括号）。
' 如果 xtime 是 "(12:30)"，那么 Result 变成 "12:30"。
Result = Mid(xtime, 2, Len(xtime) - 2)
' 将延迟添加到结果时间。
' 延迟除以 24 以将其从小时转换为一天的一部分，
' 因为在 VBA 中，日期变量的整数部分表示天数。
' 结果是新的时间标记，即原始时间加上延迟。
TimeMark = Result + delay / 24
End Function
英文:
Use Mid() to remove the parentheses and DateValue to extract the date value.
' This function takes a string representing a time and a delay (in hours),
' and returns a time mark that is the original time plus the delay.
Function TimeMark(xtime As String, delay As Double)
Dim Result As Date ' Holds the result date
' Extract the time from the string by removing the first and last character,
' and convert it to a date/time type. The assumption here is that xtime
' contains a time string enclosed in additional characters (e.g., brackets).
' If xtime is "(12:30)", then Result becomes "12:30".
Result = Mid(xtime, 2, Len(xtime) - 2)
' Add the delay to the Result time.
' The delay is divided by 24 to convert it from hours to a fraction of a day,
' because in VBA, the integer part of a Date variable represents days.
' The Result is the new time mark that is the original time plus the delay.
TimeMark = Result + delay / 24
End Function
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论