Recent Posts

Build a simple timesheet in Excel

Posted by Sinlung
Excel makes it easy to set up a system for tracking time. Follow these steps to create your own or download our sample timesheet template and customize it to fit your needs.

Microsoft Excel
Build a simple timesheet in Excel The process of tracking time is unique to every employee or position, so there's no one-size-fits-all sheet that will do the job. Still, certain principles and features will generally play into most timesheet models.
For instance, before you start formatting cells and entering formulas, you need to decide why you're tracking time, who's going to use that information, and how:
  • Decide on a time period. (Management will probably make this decision for you.) Most of us track hours weekly, biweekly, or monthly.
  • Tracking just time isn't always enough. Many companies bill your hours back to clients. Therefore, you must allocate your time to specific projects.
This article will show you how to create a weekly timesheet you can easily adjust to track biweekly and monthly times and projects. As is, this sheet doesn't document breaks or allow for flexibility in scheduling. However, the basic structure and formulas are present, so you can easily customize the sheet to fit your needs.
1: Determine your needs
When preparing a template other people will use, you need to consider several options:
  • How to solicit and validate input from users to eliminate typos and other invalid data.
  • How users will access the template--from an Excel workbook on their local systems or via a browser and a Web-based application.
  • How to protect the template sheet so users can't alter formulas and automated features.
  • How to secure confidential data.
If you're creating a timesheet for yourself, your job is somewhat less complicated.
2: Enter labels
The first step to actually building the timesheet is to enter the appropriate labels. That includes the following headings:
  • All applicable employee information, such as name, social security number, employee identification number, department, and manager. Include only data that's truly required.
  • A time period. This could be the first day of the work week, the start and end date of a bimonthly time period, or even the first day of a fiscal month.
  • Generating dates is necessary. Including the names of the workdays might seem unnecessary, but your users will probably appreciate your attention to helpful details.
  • Time in and out, breaks, sick, vacation, overtime, and so on.
  • Subtotals and grand totals, as required.
  • Employee and approving manager signature lines, if required.
A weekly timesheet for tracking hours might resemble the one shown in Figure A. Enter meaningful labels in bold, center the column headings, and apply the appropriate borders. You'll want to customize the labels to fit your specific needs.
Figure A

Enter the appropriate labels to identify your timesheet data.
3: Automate the dates
You can require users to enter the dates manually, but that leads to mistakes, even with the best trained users. If you know the exact time period, the simplest solution is to automate the required dates as follows:
  1. Have users enter the first date of the time period in cell B2.
  2. In the first cell in the Date column, A7, refer to the input date using the formula
=IF(B2<>"",B2,"")
as shown in Figure B.
Figure B

Let the sheet generate dates based on the first date of each time period.
If the time period cell (B2) is blank, this formula returns a zero-length string. If there's a value, the formula returns it. The formula will return a date serial value until we format it (which we'll do later).
  1. In cell A8, enter the formula
 =IF(A7<>"",A7+1,"")
as shown in Figure C.
Figure C:

This formula adds 1 to the value (date) in cell A7.
  1. Copy the formula in cell A8 as needed. For instance, if you're tracking time by the week, copy the formula in cell A8 to cells A9:A13 for a total of seven rows (A7:A13). For a biweekly timesheet, you'd copy the formula to cell A20, and so on.
The next step is to enter a formula in column B that returns the name of the weekday for the dates in column A. To do so, enter the simple formula =A8 in cell B7 and copy it to cells B8:B13. (Later, we'll format B7:B13 to display the day of the week by name rather than the actual dates shown in column A.) If there's no date in cell B7, the sheet will appear empty.
4: Format Date and Day of Week columns
Right now, the General format displays serial values in the Date and Day of Week columns. First, let's format the dates in column A, as follows:
  1. Select A7:A13.
  2. Right-click the selection and choose Format Cells from the context menu.
  3. On the Numbers tab, select Date from the Category list, choose the appropriate format, such as d/m/yy, from the Type list, and click OK.
Next, format the dates in column B, as follows:
  1. Select B7:B13.
  2. Right-click the selection and choose Format Cells.
  3. On the Number tab, choose Custom from the Category list.
  4. Enter dddd in the Type field, as shown in Figure D, and click OK.
Figure D

The dddd format displays a date value as its day of the week.
As you can see in Figure E, the sheet clearly denotes the dates for which you're tracking hours. Now you're ready to start adding formulas for tracking time values.
Figure E

The sheet generates dates for each time period if you provide a beginning date (cell B7).
5: Enter a formula that calculates the first eight hours of each day
At this point, we need a formula that evaluates the In and Out time values up to and including the first eight hours of each day. If overtime isn't an issue, you won't need such a complex formula. However, for most hourly employees, overtime is a possibility.
Enter the following formula into cell G7, as shown in Figure F, and then copy it to cells G8:G13:
=IF(((D7-C7)+(F7-E7))*24>8,8,((D7-C7)+(F7-E7))*24)
If the number of hours worked in one day is greater than eight, the formula returns 8. If the number or work hours is equal to or less than 8, the formula returns that amount. For now, the formula returns 0 because there are no time values to evaluate.
Figure F

This formula returns the first eight hours of each work day.
6: Enter a formula that calculates overtime for each day
Overtime constitutes hours over eight in any given day or any hours over 40 for the week. You should check your company's policy. This sheet simply tracks overtime by the day. How you compensate the employee isn't necessary at this point, as this sheet doesn't deal with wage earnings--it just tracks time.
To track overtime by the day, enter the following formula into cell H7, as shown in Figure G, and then copy it to cells H8:H13:
=IF(((D7-C7)+(F7-E7))*24>8,((D7-C7)+(F7-E7))*24-8,0)
Figure G

When the number of work hours is greater than 8, this formula returns the overage.
7: Format In and Out columns
Entering In and Out values can muck things up a bit if they're not formatted correctly, but before we format those cells, let's take a look at why you must. Figure H shows a few days' worth of In and Out values entered as general numbers. As you can see, the formulas just don't know how to handle them all.
Figure H

When calculating time, work with valid time values to avoid troublesome errors.
Now, you might think that a different set of formulas could handle general numbers. What would happen if you delete the *24 components in both formulas? Those formulas work up to a point, but neither handles all timelines. For instance, neither formula can handle an In value that's greater than its companion Out value (such as row 10 in Figure H).
To format the In and Out values appropriately, do the following:
  1. Select C7:F13.
  2. Right-click the selection and choose Format Cells.
  3. On the Number tab, choose Time from the Category list.
  4. Select the 00:00 format and click OK.
8: Provide In and Out default values
Because entering time values is prone to errors, consider providing default time values. That way, users will have fewer opportunities to introduce errors into the sheet. The default values you save with the sheet depend on each user's schedule, and you might have to provide individual sheets for many individuals. However, Figure I shows the most common 40-hour week schedule.
Figure I

Enter default time values to avoid user input errors.
The default values shown here are entered using a 24-hour clock. 13:00 is 1:00 PM (check the Formula bar) and 17:00 is 5:00 PM. You can enter 1:00 and 5:00, but you must train your users to also enter the PM. Excel will assume that 1:00 is 1:00 AM and 5:00 is 5:00 AM if you don't specify that it's PM. Either way, entering time isn't intuitive for most users, so providing the default values is helpful.
In this example sheet, Saturday and Sunday aren't regular workdays, so it stores 0 values. Of course, you can customize the days normally worked to suit each employee.
9: Enter defaults for sick and vacation time
You'll want to enter default values for sick and vacation time. In this case, just enter 0s and format as General displaying two decimal places, as shown in Figure J. Format these values as follows:
  1. Select I7:J13.
  2. Right-click the selection and choose Format Cells.
  3. On the Number tab, choose Number from the Category list and click OK.
By default, the Number format assumes two decimal places, but you might want to set that to 0. The two decimal places will allow employees to specify partial hours.
Figure J

Enter default values for sick and vacation time.
10: Enter a formula that calculates daily totals
The next step is to total the daily hours in column K. To do so, enter the following SUM() function in cell K7 and copy it to cells K8:K13, as shown in Figure K:
=SUM(G7:J7)
Figure K

This simple SUM() function totals daily hours.
11: Enter formulas that calculate weekly totals
You'll also probably want to calculate weekly totals for each category of time. To do so, enter the following function in cell G14 and copy it to cells H14:J14, as shown in Figure L:
=SUM(G7:G13)
Figure L

SUM() functions total weekly hours.
12: Validating sums
The week's total hours, which you'll want to display in cell K14, should be the same whether you sum the daily totals in column K or the hourly components in row 14. By checking both ways, you add a level of validation to the sheet. Should the totals not match, you want a formula that alerts you to the problem. Enter the following formula in cell K14, as shown in Figure M:
=IF(SUM(G14:J14)=SUM(K7:K13),SUM(G14:J14),"Error!")
Figure M

Verify subtotals using this IF() function.
13: Fine-tune the formatting
The sheet is now functional, but you'll want to add a few borders to distinguish the sections and totals. Just select each section and choose the appropriate border from the Borders drop-down palette. You might also want to add shading to the weekly total cells in row 14. Figure N shows the finished sheet.
Figure N

Add borders to separate sections.
14: Ensure valid input values
Users will have to change some data, but the sheet is specific in what types of values it needs. To protect the sheet's purpose, you can restrict users to specific types of data. For instance, cell B2 must be a valid date for the date- and day-generating formulas in columns A and B to work. Enable data validation for this cell as follows:
  1. Select cell B2.
  2. From the Data menu, choose Validation.
  3. On the Settings tab, choose Date from the Allow drop-down list.
  4. Choose Greater Than from the Data list.
  5. Enter 1/1/1900 in the Start Date field, as shown in Figure O, and click OK.
Figure O

Make sure users enter only valid values.
When validating this particular date, you can narrow things down a bit. However, if you just want to ensure that the input value is a valid date, the above works fine.
You can also ensure that the In and Out entries are valid time values, as follows:
  1. Select C7:F13.
  2. Choose Validation from the Data menu.
  3. On the Settings tab, and choose Time from the Allow list.
  4. In the Start Time field, enter 0:00. In the End Time field, enter 23:59 and click OK.
As with the date value, you can narrow down the acceptable times. You can even limit the columns individually. However, the "between 0:00 and 23:59" settings will reject anything but a valid time entry.
To protect the Sick and Vacation columns, set up validation as follows:
  1. Select I7:J13.
  2. Choose Validation from the Data menu.
  3. On the Settings tab, choose Any Value from the Allow list.
  4. Choose Decimal from the Allow list.
  5. Enter 0 and 8 as the Minimum and Maximum values and click OK.
Choosing Decimal in step 4 will allow users to enter partial hours. Choose Whole Number from the Allow list to restrict users to only hours. In addition, you can enter clues to the type of data the user must enter, as well as error messages. Applying validation rules adds the first layer of protection.
15: Protect formulas
Once the sheet is finished, add a second layer of protection by specifying what cells users can alter before you distribute it. You can do so as follows:
  1. Select a noncontiguous range consisting of the following ranges: B1:B4, C7:F13, I7:J13. (Hold down the [Ctrl] key while highlighting each range.)
  2. Choose Cells from the Format menu and click the Protection tab.
  3. Deslect the Locked option and click OK.
  4. Choose Protection from the Tools menu.
  5. Choose Protect Sheet.
  6. Enter a password.
  7. Uncheck the Select Locked Cells option and click OK.
  8. Reenter the password to verify it and click OK.
  9. Delete any test values, such as the date in B2.
  10. Save the workbook.
Now users can select only the cells they might need to update. Train your users to open the template and save a new file to create a new timesheet. That way, they can open the template with all the defaults to start each new time period.


Microsoft Powerpoint
Preflight checklist for your PowerPoint presentations Students, teachers, trainers, and business professionals of all stripes are increasingly being asked (or required) to create and deliver slide presentations. But it's not exactly a full-time job for most people.
In fact, for many users, it's a sporadic task, sometimes assigned at the last minute. That often means pushing other work aside and scrambling to remember how to add slides and speakers' notes and hoping that the text doesn't drift from blue 24-point Arial to white 44-point Calibri somewhere around the fifth slide.
PowerPoint has a few safety nets that can help with design and delivery issues, but there's still ample opportunity to overlook critical details and wind up with mistakes, inconsistencies, and unwelcome surprises at presentation time. This checklist (which you can also download as a PDF) will help you cover all the bases without having to become a PowerPoint expert. We've included a list of additional resources at the end so you can drill down on any specific tricks and techniques that interest you.
Content issues
  • Organization: Is your presentation constructed in a clear and logical way--beginning (title slide, introduction); middle (informational slides); and end (summary/conclusion)? PowerPoint's AutoContent Wizard can help you structure the show if you get stuck.
  • Objective: Does the presentation convey the necessary message/information? Is it suitable for the target audience?
  • Clarity and focus: Does each point lead logically to the next? Is every slide pulling its weight or would the presentation be tighter if you ditched a slide here and there?
  • Supporting/ancillary information: Does your presentation include hidden slides you can jump to if you need to fill time, answer questions, or amplify certain points?
  • Graphics/multimedia: Have you included charts, tables, artwork, or audio/video clips that make your presentation more interesting and help illustrate key data?
  • Supplemental material: Have you prepared handouts to distribute to your audience? This may not be necessary, but you don't want to leave it until the last minute.
Design issues
  • Consistent formatting: Have you used the same fonts and formats for common elements (titles, text boxes, bulleted lists, drawing objects) across all slides? Are the case and punctuation consistent (e.g., no ransom note capitalization or arbitrary periods after titles or phrases)? Have you applied a theme or background style to all the slides (or used a template) to create a unified design for the presentation?
  • Legible text: Have you kept the words on your slides to a minimum, letting them serve as cues for elaboration? Remember that the fastest way to lose your audience is to read slide text to them verbatim. Make sure you haven't crammed too much text on a slide. (PowerPoint makes it easy to move excess text onto its own slide.) Also check that you've used a large enough font in a readable color and there aren't any conflicting background colors or designs.
  • Accuracy: Have you checked spelling, verified names, and tested any links you've included on your slides?
  • Speaker's notes: Did you prepare some notes that will help you remember to say everything you want to say? If you put your key ideas and facts into PowerPoint's speaker's notes pane, you can refer to them during the show and optionally print them for audience handouts.
  • Transitions/animations/sounds: Have you tested any transitions, animations, and sound effects to make sure they work the way you want? Did you limit yourself to only the effects that make the information easier for your audience to grasp (as opposed to running amok with spins, fades, dissolves, and canned applause and drum rolls)?
Delivery issues
  • Timing: Have you rehearsed your presentation to make sure the timing is about right--with opportunities for Q/A, if appropriate? PowerPoint has a built-in rehearsal feature that will record the time you spend on each slide. Are you ready to fill time or cut to the chase if things run too short or too long?
  • Logistics/equipment: Have you verified that you'll have the necessary equipment at the presentation site (or made arrangements to bring your own)? A visit to the site ahead of time may help you spot any potential problems with the projector, power supply, physical layout, and so on. If you do run into a technical snag during the presentation, you may still be able to fix the issue and move on.
  • Fonts, supporting files: Does your presentation include all the components necessary to run properly if you'll be using someone else's system? To be on the safe side, you may want to use PowerPoint’s Package for CD tool, which lets you put everything you might need--image files, video clips, TrueType fonts, sound files, and other files used by the presentation--onto a CD or into a folder for easy transport.
  • Basic navigation techniques: Do you know how to launch the slideshow and go to the next or previous slide in a presentation? Can you jump to a specific slide if necessary or black out/white out the display temporarily? If you follow a link away from the presentation, can you find your way back? Can you navigate with both keyboard and mouse? If you don't use PowerPoint very often, some last-minute practice and a simple cheat sheet will come in handy.
  • Speaking skills: Are you well versed in the material you'll be presenting? Have you rehearsed what you plan to say and practiced coordinating your speech with your slide navigation? Even if you're mortally afraid of public speaking, reviewing some best practices may help you smooth some rough edges off your delivery.


Microsoft Office
An efficient method for adding a text box to a Word document The usual routine for entering a text box into a Word document is to select the spot where you want the text box to appear, choose Text Box from the Insert menu, drag the insertion point until the text box is about the right size, and then enter the text.
This method is a bit inefficient, because you usually have to resize the text box a bit. It's difficult to get the size just right before you actually enter the text.
I like to enter the text and then insert the text box. That way, the text box adjusts to the text automatically. The approach seems a bit backward, but it's more efficient. Here's how to enter a text box based on the text:
  1. Enter the text in the document anywhere you like. You can move the text box later.
  2. Select the text.
  3. Choose Text box from the Insert menu.
It's that simple. Word inserts and fills the text box using the selected text and adjusts the size of the text box, accordingly. This method is more efficient than the more traditional method of filling an existing text box.