Accumulating time working on a spreadsheet

[GallowayJay]

I work with Excel files often. On occassion, I am asked how many hours/minutes I put into a specific file. So, if I spend 2 hours in a spreadsheet, I want a calculation that will add up the time, and display 2 hours. Does someone know how to calculate the exact you spend in a specific spreadsheet? I don't want to use the TODAY() function as it will automatically update with incorrect data. Thanks as always for all the help.

 

[Brian from Maui]

Jay,

I doubt this is what you want but this may help. In properties and the statistics tab, there is a total editing time.

 

[Brian from Maui]

Jay,

Upon further research, Excel does not use the Total Editing Time in the Statistic tab

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q214393&

Sorry, back to square one. This will bump the thread to the top however.

 

[ylijohe]

Sub startingtime()
'
' startingtime Macro
' Macro recorded 19.9.2002 by YLIJOHE
'
' Keyboard Shortcut: Ctrl+Shift+S
'
ActiveCell.FormulaR1C1 = "=NOW()"
Selection.Copy
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub
Sub STOPtimecount()
'
' STOPtimecount Macro
' Macro recorded 19.9.2002 by YLIJOHE
'
' Keyboard Shortcut: Ctrl+Shift+T
'
Range("A2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=NOW()"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C"
Selection.NumberFormat = "mm:ss.0"
Range("A1:A3").Select
Selection.NumberFormat = "h:mm"
End Sub

Is this what You are looking for?
Obviously You are looking for something that would ADD the hours form your previous editing sessions and THIS session together.
These Two functions give hours from THIS session, all You have to do is summ it somehere to TOTAL HOURS?
This message was edited by ylijohe on 2002-09-19 01:05

 

[Derek]

Hi Jay
You might like to try these two workbook event macros. They accummulate time the workbook has been open in A1 of Sheet1 (which you could hide then reference A1's value via a macro button and messagebox).
Custom Format A1 as [h]:mm:ss

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheet1.Range("A3").Value = Time
Sheet1.Range("A4").Value = Sheet1.Range("A3").Value - Sheet1.Range("A2").Value
Sheet1.Range("A1").Value = Sheet1.Range("A1").Value + Sheet1.Range("A4").Value
End Sub

Private Sub Workbook_Open()
Sheet1.Range("A2").Value = Time
End Sub

hope this gives you some ideas

regards
Derek

PS The first time you set this up you will need to close it, then reopen it again, then delete everything except the open time in cell A2. Thereafter it should work fine
This message was edited by Derek on 2002-09-19 01:15