accumulate time file is open

[RAMJET]

i am working on files in excel where i need to keep track of how long the
files have been open, so that I can complete a timesheet for billing
purposes. Is there anyway to track this information within the excel file(s)
itself?

 

[Gord Dibben]

Insert a new worksheet in your workbook.

In A1 enter Date
In B1 enter Time Opened
In C1 enter Time Closed
In D1 enter Working Time

In D2 enter =IF(C2="","",C2-B2)

In Thisworkbook module paste this code.

Private Sub Workbook_Open()
Dim rng As Range
Set rng = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
With rng
.Value = Format(Date, "mm/dd/yyyy")
.Offset(0, 1).Value = Format(Time, "hh:mm:ss")
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim rng As Range
Set rng1 = Sheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
If rng1.Offset(0, -1).Value = "" Then Exit Sub
rng1.Value = Format(Time, "hh:mm:ss")
ThisWorkbook.Save
End Sub


Gord Dibben MS Excel MVP