Looking for Work Load/Production Report Logs spreadsheet...

  • Thread starter Thread starter Greg
  • Start date Start date
G

Greg

Hi,

We are being asked to keep track of our daily work load. We need to
record start and stop times as well as total time spent working on
each job. It seems Excel would be perfect for this task because of
it's Current Date and Time functions. Does anyone know whether this
has already been invented and where I can find a sample Excel
Worksheet? Or any assistance you can provide to make this mundane task
less time consuming than writing it down manually is appreciated.

Thanks in advance for your help.

-Greg
 
Hi Greg,

The only way I can think of to do what you want is with an event driven
macro. I have prepared 3 different ones for you to try.

Open a new workbook and right click on the worksheet tab name. Select View
Code and the macro editor will open.

Copy ONE ONLY of the 3 macros below and paste into the large white area of
the editor.

Close the macro editor by clicking the X with the red background far top
right. (Ensure that you click the top X because the one below it only closes
the specific macro sheet.)

Save the workbook.

Double Click any cell on the worksheet and the relevant data will be entered
in the cell.

The macros can be modified to run only when specific Rows, Columns or Ranges
are Double Clicked. If you want this then get back to me but I suggest that
you develop your worksheet first so that you will then know what ranges you
want it restricted to.

The line in the macro with the number formatting can be modified to any
valid number format. Alternatively, you can delete the line and format the
required cells on the worksheet.

The macro only runs with the worksheet that you right clicked to open the
macro editor. If you want to try the other macros, select another worksheet
and right click the tab name and do as above.


Macro 1: (Copy from Private Sub to End Sub)

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)

Cancel = True 'Cancels edit mode of the cell

'Format the cell to display time and date
Target.NumberFormat = "hh:mm ddd dd mmm yyyy"

'Assign current time and date to the cell
Target.Value = Now()

End Sub


Macro 2:-

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)

Cancel = True 'Cancels edit mode of the cell

'Format the cell to display time and date
Target.NumberFormat = "[hh]:mm"

'Assign current time and date to the cell
Target.Value = Time

End Sub


Macro 3:-

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)

Cancel = True 'Cancels edit mode of the cell

'Format the cell to display time and date
Target.NumberFormat = "dd mmm yyyy"

'Assign current time and date to the cell
Target.Value = Date

End Sub
 
Hi again Greg,

I just realized that I did not modify the comments in the macros. (Comments
show in green when you copy them into the editor). The macros will still do
what they are meant to do; it is only the comments that are wrong.

Comments in the the first Macro are correct.

The second one should be:-
'Format the cell to display time
and
'Assign current time to the cell

The third one should be:-
'Format the cell to date
and
'Assign current date to the cell


--
Regards,

OssieMac


OssieMac said:
Hi Greg,

The only way I can think of to do what you want is with an event driven
macro. I have prepared 3 different ones for you to try.

Open a new workbook and right click on the worksheet tab name. Select View
Code and the macro editor will open.

Copy ONE ONLY of the 3 macros below and paste into the large white area of
the editor.

Close the macro editor by clicking the X with the red background far top
right. (Ensure that you click the top X because the one below it only closes
the specific macro sheet.)

Save the workbook.

Double Click any cell on the worksheet and the relevant data will be entered
in the cell.

The macros can be modified to run only when specific Rows, Columns or Ranges
are Double Clicked. If you want this then get back to me but I suggest that
you develop your worksheet first so that you will then know what ranges you
want it restricted to.

The line in the macro with the number formatting can be modified to any
valid number format. Alternatively, you can delete the line and format the
required cells on the worksheet.

The macro only runs with the worksheet that you right clicked to open the
macro editor. If you want to try the other macros, select another worksheet
and right click the tab name and do as above.


Macro 1: (Copy from Private Sub to End Sub)

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)

Cancel = True 'Cancels edit mode of the cell

'Format the cell to display time and date
Target.NumberFormat = "hh:mm ddd dd mmm yyyy"

'Assign current time and date to the cell
Target.Value = Now()

End Sub


Macro 2:-

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)

Cancel = True 'Cancels edit mode of the cell

'Format the cell to display time and date
Target.NumberFormat = "[hh]:mm"

'Assign current time and date to the cell
Target.Value = Time

End Sub


Macro 3:-

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)

Cancel = True 'Cancels edit mode of the cell

'Format the cell to display time and date
Target.NumberFormat = "dd mmm yyyy"

'Assign current time and date to the cell
Target.Value = Date

End Sub


--
Regards,

OssieMac


Greg said:
Hi,

We are being asked to keep track of our daily work load. We need to
record start and stop times as well as total time spent working on
each job. It seems Excel would be perfect for this task because of
it's Current Date and Time functions. Does anyone know whether this
has already been invented and where I can find a sample Excel
Worksheet? Or any assistance you can provide to make this mundane task
less time consuming than writing it down manually is appreciated.

Thanks in advance for your help.

-Greg
Click to expand...
Click to expand...
 
Back
Top