Excel SUM formula doesn't work for hours from VBA

Question

I have VBA that counts time spent on the project and after user is pressing STOP button it enters time into other sheet. Everything is working fine expect for SUM formula to calculate total amount of hours spent. Formula =SUM(A2:A15290) gives value of 00:00:00 (zero) in cell A1. I have tried to "Format cell" in different ways but nothing works. If I enter time manually to cells, then everything works fine. Maybe problem is in my VBA that is entering counted time to cells?

Here is the macro I am using:

Option Explicit

Sub StartTimer()

    Dim Start As Single, RunTime As Single
    Dim ElapsedTime As String

    'Set the control cell to 0 and make it green
    Range("Y18").Value = 0
    Range("Y14").Interior.Color = 5296274 'Green

    Start = Timer    ' Set start time.
    Debug.Print Start
    Do While Range("Y18").Value = 0

        DoEvents    ' Yield to other processes.
        RunTime = Timer    ' current elapsed time
        ElapsedTime = Format((RunTime - Start) / 86400, "h:mm:ss")
        'Display currently elapsed time in A1
        Range("Y14").Value = ElapsedTime
        Application.StatusBar = ElapsedTime

    Loop

    Range("Y14").Value = ElapsedTime
    Range("Y14").Interior.Color = 192 'Dark red
    Application.StatusBar = False

End Sub

Sub StopTimer()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
    'Set the control cell to 1
    Range("Y18").Value = 1

Set copySheet = Worksheets("MAIN")
Set pasteSheet = Worksheets("Time Spent")

copySheet.Range("Y14").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Unknown Unknown · Accepted Answer · 2018-10-03T07:11:43

You are putting text-that-looks-like-time into the cells. Use real time.

Dim Start As DOUBLE, RunTime As DOUBLE
Dim ElapsedTime As DOUBLE

...

Start = Timer    ' Set start time.

...

    DoEvents    ' Yield to other processes.
    RunTime = Timer    ' current elapsed time
    ElapsedTime = (RunTime - Start) / 86400

    Range("Y14").Value = ElapsedTime
    Range("Y14").numberformat = "[hh]:mm:ss"

Remember to be careful about a start and end that cross midnight. Timer resets at midnight and Excel doesn't like negative time.

Excel SUM formula doesn't work for hours from VBA

1 Answers