• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to track time spent on Excel each day...VBA or free app?

PP3321

Active Member
Dear Chandoo Community,

Thank you always for sharing your knowledge and expertise...
Today I have 1 problem...

I need to track how many hours I am spending on Excel each day.

Would you suggest writing a VBA code to do this,
or should I get free app (I'm using Windows)

*Maybe I can use a code like this to simply enter date and time everytime I open workbook and close...
Code:
Private Sub Workbook_Open()

Workbooks.Open "Time Tracker.xlsx"
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = Date
Cells(Rows.Count, 2).End(xlUp).Offset(1, 0) = Time

    Application.DisplayAlerts = False
    Workbooks("Time Tracker.xlsx").Save
    Workbooks("Time Tracker.xlsx").Close
    Application.DisplayAlerts = True

End Sub

I would be extremely grateful if you could recommend a tool for this...

Thanking you in advance...
 
There are some challenges ... or not?
like:
1) Do You use one or more files per day?
2) How many files do You use in one time?
My sample would work ...
'TimeTracker.xlsx'-flle has to save to 'good' place that it can find .. or You should add its path too.
'Workbook3.xlsb' has those Open & BeforeClose,
which have to copy to ALL used/tracked files as that file.
All 'Your files' can use same 'TimeTracker.xlsx'-file.
You can get used times per filename and daily values too.
>> Ideas ... Questions ...?
... of course, this needs more testing!
 

Attachments

  • TimeTracker.xlsx
    30.9 KB · Views: 22
  • Workbook3.xlsb
    35.6 KB · Views: 20
@PP3321
This version would notice
if You have more than one 'tracking' file open in same time and
'maybe' this would notice if You're working in night shifts (over midnight) too.
 

Attachments

  • TimeTracker.xlsx
    41.7 KB · Views: 28
  • Workbook3.xlsb
    44.3 KB · Views: 23
@Monty
Thank you so much for this. I like your solution.
I moved the line to calculate the MinutesElapsed inside BeforeClose event.

Code:
Public StartTime As Double
Public MinutesElapsed As String

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
MsgBox "Time spent on This workbook " & MinutesElapsed, vbInformation
End Sub

Private Sub Workbook_Open()
StartTime = Timer
End Sub
 
... and You
I need to track how many hours I am spending on Excel each day.
and bonus 'notice overlaps' too.
Thanks!
 
Back
Top