Excel timesheets

To create the timesheet Using your timesheet Added options Date tips

Helen Bradley helps you keep track of the hours you work with a handy timesheet and also looks at how dates are stored and managed in spreadsheets.

The timesheet we are going to create allows you to differentiate between billable and non-billable hours and to separately total these each week.

To create the timesheet

Step 1: Type these entries into the named cells:

Play ScreenCam tutorial Part 2

Figure 1: The timesheet allows you to record and total your billable and non-billable hours for a working week.

The area D6:I25 will look better formatted to hide zero values. To do this, use Format, Cells, select the Number tab, in the Category: list select Custom and in the Type box type (in Excel 5 use Format, Cells, select the Number tab, in the Category list select Number and key this into the Code box):

0.00;(0.00);

and click OK. This displays all numbers to two decimal places, negative numbers in parentheses and hides any zero values.

Format cells I2 and D4:H4 as dates and then format the remainder of the worksheet neatly, use Figure 1 as a guide.

Step 3: Enter details of your clients and usual 'Other time' categories into the worksheet and save the file calling it wklytime.xls.

Using your timesheet

You can now use the timesheet to record your weekly time expended on various tasks. Enter Monday's date in cell I2 and the times as decimal numbers. So if you work for two-and-a-half-hours on a project enter this as 2.5. This, for most people is the simplest way of entering times, particularly as you can use the numeric keyboard to enter them.

If, however, you want to enter your times as hours and minutes, you can enter 2:30 for two-and-a-half-hours. Note that you must use a colon between the numbers and you must add the colon for whole hours, for example, enter 3:00 for three hours (not 3 - as this is entered as three days!). There is another anomaly to watch out for: always enter 3:40 for three hours forty minutes (try entering 3:4 and you'll see it displays as 3:04!). To show the timesheet data in the correct format, highlight the area D6:I25 and format it using this custom format (see Step 2 for how to do this):

[h]:mm;;

This format shows hours and minutes and hides any zero entries. You'll find you will be unable to enter negative times using this method, but this may not concern you. In Figure 2 the times have been entered using hours and minutes format rather than decimal numbers.

Added options

If you have a fixed client list you can add a formula to enter client numbers in column B and have the client names appear automatically in column C. To do this, add a second worksheet to your file called Client Codes and in a list beginning in column A row 3 place the client code in one column and the Client name in the one next to it, see Figure 3. Highlight the area containing your client codes and names and one extra row and name it by selecting Insert, Name, Define and keying in the name NamesAndCodes. Now highlight the codes only and one extra row (column A) and name it, using the same process, ClientCodes.

Using Format, Cell, Patterns, colour the area around the client codes so that only the named area shows. This will mark the area for you and remind you to use Insert, Row to add new rows so that they will be included in the named areas. Figure 3 shows what your sheet should look like.

Now add this formula to cell C6 of your timesheet:

=IF(B6<>"",INDEX(NamesAndCodes,MATCH(B6, ClientCodes,0),2),"")

Copy this formula from cell C6 to cells C7:C15. Now when you enter a valid client number in a cell in column B, the client name will appear in the cell next to it. If an invalid client number is entered, then the error will be flagged by a #N/A entry in the cell.

This formula looks for an exact match for the client number in the table on the second sheet. If an exact match is found then the data in column 2 of the table, the client name, is returned. Using INDEX and MATCH means that the formula will return a client name only if there is an exact match and notwithstanding that the data in the table isn't sorted in client number order.

Date tips

How dates and times are stored

In Excel dates are stored as a number representing the number of days that have elapsed since the 1st January, 1900. So 1st January 1900 is day 1 and 3rd March, 1997 is day 35492. Times are decimal fractions (parts of a day) so 12pm on the 3rd of March 1997 is 35492.5.

Entering dates and times

You can enter dates into Excel in a number of ways, including 30-Mar-97, 30/3/97, 30/3, 30-Mar, etc. If you omit the year, the current year is assumed. If you omit the day of the month, for example, by entering 3/97 the first day of the month is assumed.

Some handy time conversions

Convert from a decimal number to hours and minutes. This formula takes a number in the format 7.5 from cell A6 (representing 7.5 hours) and turns it into 7:30, format the result as Time using the format: h:mm so it displays correctly: =A6/24

Convert from hours and minutes to a decimal number

To convert a number in cell A7 which is entered in hours and minutes format, for example, the entry 7:30 represents 7 hours 30 minutes, to a decimal number, multiply the cell by 24 and format the result as a number with two decimal places: =A7*24

Convert from hours and minutes (as a decimal number) to a decimal number

To convert a number in cell A8 which is entered in hours and minutes format (for example, the cell entry 7.30 is set to represent 7 hours 30 minutes) to a decimal number, use this formula: =INT(A8)+MOD(A8*100,100)/60

Are your dates correct?

If Windows has not been installed correctly you may find your dates showing in mm-dd-yy format (also called US date format) rather than the more usual dd-mm-yy format. To alter Windows to show dates correctly: In Windows 3.1 select Control Panel, International, Date Format, and set the Short Date Format to DMY and the long date format to DMY (see Figure 4); in Windows 95 select Start, Settings, Control Panel, Regional Settings and select English (Australian). Click on the Date tab and select your preferred date format for both short and long dates and click OK.

Today's date and time and 'right now'

Press Control + ";" to enter today's date.

Press Control + Shift + ":" to enter the current time.

To always display the current date and/or time (depending on the format you are using) use the formula =Now()

Top of page

|What's New | Net Guides | Web Workshop | Net Sites | About PC User |
| Games | Education | General & Business | Online Tools | Utilities |
| Patches & Support files | PC User Interactive |

All text © 1997 Australian Consolidated Press - PC User Magazine