英文:
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 Integerxtime = xtime + delay / 24S = 0M = 0H = 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 Integerxtime = xtime + delay / 24S = 0M = 0H = 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 DateTimeMark = CDate(Replace(Replace(xtime, "(", ""), ")", "")) + delay / 24End 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 DateTimeMark = CDate(Replace(Replace(xtime, "(", ""), ")", "")) + delay / 24End 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 / 24End 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 / 24End Function
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论