Timer In Status Bar

[Dazzawm]

Is there anyway that a timer can be put in a workbook and counts up every time its opened? When I start a new project I want to see how long it takes, so from the moment I start it I want it to count up and then stop when I close it. Then when I open it again the next day I want it to continue from where it left of and so on. Possible? Thanks.

 

[Warship]

After saving the book ane re-opening do you still get the error?

 

[Dazzawm]

After saving the book ane re-opening do you still get the error?

Well the error doesn't come up any more and I can see session time and total time in the task bar but it doesn't count up, it stays at zero.

 

[Dazzawm]

Well the error doesn't come up any more and I can see session time and total time in the task bar but it doesn't count up, it stays at zero.

Sorry it does count up by the minute. I was expecting to see it count up by the second etc..

 

[Warship]

This counts by seconds.

Depending on what you're doing with the workbook,
it may become a nuisance however.

If Excel closes abnormally, that session's time will not
be added to Total Time.

The initial setup error has been fixed.

Option Explicit
Dim StartTime
Dim EndTime
Public SessionTime
Public TotalTime
Const updateSecs = 1 'interval of seconds to update status bar

Sub UpdateProjTime()
    StartTime = Evaluate("StartTime")
    TotalTime = Evaluate("TotalTime")
    EndTime = Now
    SessionTime = EndTime - StartTime
    Application.StatusBar = "Session Time: " & Application.WorksheetFunction.Text(SessionTime, "[h]:mm:ss") _
             & " | Total Time: " & Application.WorksheetFunction.Text(TotalTime + SessionTime, "[h]:mm:ss")
    TimerStart
End Sub

Sub ProjectTimeSetup()
    Dim arr As Variant, n As Variant
    arr = Array("StartTime", "TotalTime")
    For Each n In arr
    If IsError(Evaluate(n)) Then _
        ThisWorkbook.Names.Add Name:=n, RefersTo:=0
    Next n
End Sub

Sub TimerStart()
    Application.OnTime _
        EarliestTime:=Now + TimeSerial(0, 0, updateSecs), _
        Procedure:="UpdateProjTime"
End Sub

Option Explicit

Private Sub Workbook_Open()
    ProjectTimeSetup
    ThisWorkbook.Names("StartTime").RefersTo = Now
    UpdateProjTime
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If IsError(Evaluate("TotalTime")) Then Exit Sub
    UpdateProjTime
    ThisWorkbook.Names("TotalTime").RefersTo = TotalTime + SessionTime
End Sub

 

[Dazzawm]

That seems to work good, thanks for all your help.

 

[Dazzawm]

And this goes beyond 24 hours as you mentioned this was a problem originally?

 

[Warship]

yes

 

[Dazzawm]

Thanks again.

 

[Warship]

You are welcome.

 

[davidhall]

Warship,

I have a question for you. I have integrated this into one of the spreadsheets I use to track time spent on a project. I am needing assistance on two issues.

1). When I exit out of the workbook, it asks me if I want to save the workbook. Whether I click yes or no, the workbook doesn't close. The only way to get the workbook to close is by exiting excel entirely.

2). I want to use this code on a master template. When an employee opens the template and saves the data they are analyzing, the saved version will keep the session time and total time tracked and the master template would need to reset. Any suggestions on how to make that work?