累计时间计算结果为48小时，而不是24小时

Question

这让我压力很大。我有一个工作表，用户可以手动或通过按钮自动输入时间。在B栏中以24小时格式输入时间。

按钮代码为：

Sub TimeStamp()
    If ActiveCell.Column = 2 Then
        ActiveSheet.Unprotect Password:="80286"
        With Selection
            .Value = VBA.DateTime.Now
            .NumberFormat = "hh:mm"
        End With
        ActiveSheet.Protect Password:="80286"
    End If
End Sub

工作表上的公式如下：

START TIME V16 =CEILING(IF(TIMEVALUE(TEXT(B16,"hh:mm"))=B16, O9 + B16, B16),0.5/24)获取通过按钮或手动输入的时间。它的格式是custom (m/d/yyyy h:mm AM/PM)。

CURRENT entry V17 =IF('TR2'!B10='TR2'!B11,'TR2'!B11,'TR2'!B10)从公式=IFERROR(LOOKUP(2,1/(B16:B63<>""),B16:B63),"0:00")中获取B列中的最后一项，其格式为custom (hh:mm)。

END TIME V18 =CEILING(IF(TIMEVALUE(TEXT(IF('TR2'!B10='TR2'!B11,'TR2'!B11,'TR2'!B10),"hh:mm"))=IF('TR2'!B10='TR2'!B11,'TR2'!B11,'TR2'!B10), TODAY() + V17, IF('TR2'!B10='TR2'!B11,'TR2'!B11,'TR2'!B10)),0.5/24)获取通过按钮或手动输入的时间。它的格式是custom (m/d/yyyy h:mm AM/PM)。

ACCRUED V20 =IF(V19>0,MAX(0,(V19-V16)*24),MAX(0,(V18-V16)*24))。格式化为general。

这一切都很好，我的问题是当时间来自m/d/yyyy 0:00 - m/d/yyyy 23:59时保存工作簿时，这会将结束日期切换到第二天，这很好，因为我对累积时间的所有计算都是24。(7/25/2020 0:00 - 7/26/20202 0:00)

该工作簿将在将来打开，因此我必须尝试从单元格V18中删除Today()函数。因此，当它保存时，它使用VBA更改这些值：

Worksheets("TR").Range("V21").Value = Format(CDate(Worksheets("TR").Range("O10").Value), "mm/dd/yyyy")
Worksheets("TR").Range("V19").Formula = "=CEILING(V21 + TIMEVALUE(TEXT(V17,""hh:mm"")),0.5/24)"

O10的公式是=IF(V19>0, V19,V18)，格式为date (month day, year)。

显示为2/27/2020 12:00 AM的V19 =CEILING(V21 + TIMEVALUE(TEXT(V17,"hh:mm")),0.5/24)。格式化为custom (m/d/yyyy h:mm AM/PM) V20的公式=IF(V19>0,MAX(0,(V19-V16)*24),MAX(0,(V18-V16)*24))将显示48。V21将显示2020-07-26。格式化为Date。

现在，这就是我的悲哀之处。Excel将其改为48小时的7/27/2020 0:00，而不是24小时的7/26/2020 0:00。

更多信息；此工作表还可以使用旧日期。您可以编辑开始(手动输入)和结束日期，而不是使用今天，编辑结束日期使用来自用户表单的以下代码：

Private Sub CommandButton1_Click()
    If IsDate(Edit_Date.TextBox2.Value) = False Then
        MsgBox "Date entered is not valid." & vbNewLine & "Enter date as ""MONTH - END DAY - YEAR.""", , "DATE ERROR"
        Exit Sub
    End If
    ask = Format(CDate(TextBox2.Text), "mmmm/dd/yyyy")
    If MsgBox("Change end date to " & ask & "?", vbYesNo, "EDIT END DATE") = vbNo Then Exit Sub
    Worksheets("TR").Unprotect Password:="80286"
    Worksheets("TR").Range("V21").Value = Edit_Date.TextBox2.Value
    Worksheets("TR").Range("V19").Formula = "=CEILING(V21 + TIMEVALUE(TEXT(V17,""hh:mm"")),0.5/24)"
    Worksheets("TR").Protect Password:="80286"
    Unload Me
End Sub

当我试图修复一个函数以显示正确的累积时间时，另一个函数中断了。有没有更好、更有效的方法来做到这一点？感谢您的时间，我为我的文字墙道歉，我想尽我所能展示。

Answer 1

我的女朋友给了我一个天才的笔触，我不必要地把这个问题复杂化了。她说如果Today()制造了这个问题，就把它移除，所以我就这么做了。

公式和VBA的更改如下所示，如果有人对解决方案感兴趣，请张贴。

Enter Time按钮：

Sub TimeStamp()
        If ActiveCell.Column = 2 Then
            ActiveSheet.Unprotect Password:="80286"
            With Selection
                .Value = Time
                .NumberFormat = "h:mm"
            End With
            ActiveSheet.Protect Password:="80286"
        End If
    End Sub

开始日期V16 =CEILING(IF(TIMEVALUE(TEXT(B16,"h:mm"))=B16, O9 + B16, B16),0.5/24)仍然确定时间是手动输入的还是使用按钮输入的，并向上舍入到最接近的半小时。

CURRENT TIME V17 =CEILING(IF('TR2'!B10='TR2'!B11,'TR2'!B11,'TR2'!B10),0.5/24)，从单元格中拉出以确定最后一个条目B15 =IFERROR(LOOKUP(2,1/(B16:B63<>""),B16:B63),"0:00")，并向上舍入到最近的半小时。

END DATE V18 =V19+V17将来自V19的日期和来自V17的时间组合在一起。

从START DATE V19 =INT(V16)开始的日期。

ACCRUED V20 =MAX(0,(V18-V16)*24)。

对用户表单所做的更改以更改结束日期：

Private Sub CommandButton1_Click()
    If IsDate(Edit_Date.TextBox2.Value) = False Then
        MsgBox "Date entered is not valid." & vbNewLine & "Enter date as ""MONTH - END DAY - YEAR.""", , "DATE ERROR"
        Exit Sub
    End If
    ask = Format(CDate(TextBox2.Text), "mmmm/dd/yyyy")
    If MsgBox("Change end date to " & ask & "?", vbYesNo, "EDIT END DATE") = vbNo Then Exit Sub
    Worksheets("TR").Unprotect Password:="80286"
    Worksheets("TR").Range("V19").Value = Edit_Date.TextBox2.Value
    Worksheets("TR").Protect Password:="80286"
    Unload Me
End Sub

已删除保存代码。

我希望这对任何需要它的人都有帮助。我感谢那些仔细阅读并考虑回复的人。