Microsoft Excel Tips
and Tricks
Build a Timesheet with a Simple but Powerful Function in
Excel
Excel offers a simple yet powerful way to collect
employees' timesheet entries: the NOW() function. Using this
function, you can create a macro that enables an employee to
clock in or clock out with the click of a button.
To record a macro that enters and updates the NOW()
function:
1.
On the Tools menu, point to Macro, and then
click Record New Macro.
2.
In the Macro name box, enter a name for the macro,
such as "Timesheet".
3.
In the Store macro in box, click the location where
you want to store the macro. If you want a macro to be
available whenever you use Excel, select Personal Macro
Workbook.
4.
If you want to include a description of the macro, type it
in the Description box.
5.
Click OK.
6.
In the worksheet, select the cell in which the employee's
clock-in time should appear, type the formula =Now(), and
press ENTER.
7.
Copy the cell.
8.
Right-click the same cell, and click Paste Special on
the shortcut menu. Under Paste, select Values,
and then click OK. Doing this freezes the
clock-in/out time so it cannot be altered by the employee.
9.
Press ENTER.
10.
On the Stop Recording toolbar, click Stop
Recording.
Now you have a macro that updates a selected cell with the
current time. The next step is to assign that macro to a
button, so that the entry can be accomplished with a single
click.
To create a custom toolbar button and assign the new macro:
1.
On the Tools menu, click Customize, and then
click the Commands tab.
2.
In the Categories box, click Macros.
3.
Drag the Custom Button icon from the Commands
box to a toolbar. Leaving the Customize dialog box
open, do the following:
a.
Right-click the new button and then type a name, such as "ClockInOut",
in the Name box on the shortcut menu.
b.
Right-click the new button, click Change Button Image,
and then click an image. Or, to display the button name
instead of an image, click Text Only (Always).
c.
Right-click the new button and click Assign Macro.
Under Macro Name, click the name of the macro you
just created, and then click OK.
4.
Close the Customize dialog box.
Now all the employee has to do is select the appropriate
cell and click the "Clock In/Out" button.
|