Little Tutorial for Lotus 1-2-3 Timesheet Spreadsheet ===================================================== Chris Randle (chris@amlog.demon.co.uk) - 12 Nov 99 I'm sure I don't have to tell you that you use this speadsheet at your own risk. It's served me well for a couple of years, but that's not to say there are no errors in there somewhere. Files ----- You should have two files: time.txt - the one you're reading time.wk1 - the timesheet spreadsheet Use time.wk1 as a master template, and copy it as you need it. Copy it now to test.wk1 and use that file (test) in the examples. Set up ready for use -------------------- Before you start using the spreadsheet, you must enter a few details. If these details are fairly constant for you, then you may like to edit them in the master template too. The template has a locked title section. Clear the titles and then enter some details: Clear titles with [Menu] W T C Enter a description for the sheet in cell A1. Enter no. of working hours/day in cell E2. Enter your hourly rate in cell B3. Put the title back. Go to row 7 and type [Menu] W T H Save your spreadsheet again and you're ready to use it. Below I explain how I use the sheet and why. Of course, feel free to ignore or change anything that doesn't work for you. When entering examples below that begin with ' (Shift-6) you must type it. Using the TimeSheet ------------------- Enter a day and date in cell A7. You could type Fri [Fn-DATE] This might show 'Fri 12/11/99. You'll notice that the width is formatted so that when you type a time in the next column, the 99 disappears, but not the /. I have a reason for that. For subsequent dates I might put Sat [Fn-DATE] and get 'Sat 13/11/99. I then backspace out the /99 because it's the same year. It looks slightly neater. I can still see the / from the 1st line to remind me that that cell contains the year or a year change. In the next column, cell B7, enter a start time with '[Fn-TIME]. Similarly in C7 enter a stop time the same way. A description goes in cell L7, and you're ready to start a new row. The description cell is the last in the row, so you can type up to 254 (I think) characters, and still see them. To switch between Dates & Times and Costs & Descriptions, use [Ctrl-CursorRight] and [Ctrl-CursorLeft]. Alternatively zoom out [Fn-ZOOM]. What you get calculated ----------------------- Cells D7 & F7 show the hrs:mins for this row (see -MN below). Cell K7 shows the cost of this row. Cells D4, E4 & F4 show the total of working days and hours for this sheet (see -MN below). Cell K1 shows amount still to invoice (see I? below) Cell K2 shows amount invoiced to date (see I? below) Cell K3 shows cost of discounted work (see -%) Cell K4 shows the total of K1 & K2 Extra Bits ---------- I find these extra bits give some flexibility. -MN Column E has a -MN header. Here you enter the number of minutes to subtract from the two times before calculating the cost. Sometimes I work on something from 10am until 1pm, but I stopped for 20 mins to have a bite to eat, and then later, 10 mins to read a book. Rather than enter three rows with three start and stop times, you can enter 30 in the E column, and 30 minutes will be excluded from the calculations. I don't do this, but you can enter negative times here too. Say you worked as above, then another 20 mins at a later time, put -20. Or just use it to enter straight durations, e.g. one hour's work at an unspecified time by entering -60. Notice that cell E4 shows the total for this "slack time" in working hours and days, and column F shows the hrs:mins from column D after the adjustment. -% Column G has a -% header. Here you can enter your discounted rates. If you decide to be generous and knock off 50% for this row, you enter .5. If it was a freebie, enter 1. I sometimes use this if I agree to fix a bug that I think really shouldn't have been there. I enter a 1 in -%. The spreadsheet still shows that I did it, and the time that I spent but the customer doesn't get charged. The cost in column K will reflect this discount. In addition, the cell K3 shows the total "cost" of discounted work. This lost cost is *not* included in any of the other grand total cells, K1, K2 or K4. Cell K3 is just there for your information. I? Column I has a I? header. Enter a Y in this column if you've invoiced for this row. Leave blank otherwise. You can block a section as invoiced by copying one Y down as many rows as you need. The invoiced and to be invoiced totals are shown in cells K1 and K2 with the total of both in cell K4. If I'm marking a block of rows as invoiced, I sometimes record the invoice number in the description of the last row of that block. Finally ------- Well, that's about it. There is one last little trick I use the sheet for. Say you've done some work for someone and you're asked to do the same sort of work again. You think it'll take roughly the same time, but you have to squeeze it into your existing schedule. You think you can find 2 hours a day to do the work. Alter cell E2 (working hours per day) to be 2 and read off from cell F4 how many working days and hours you can expect to take to complete the job. When I print the sheet out, I sort-of cheat, because I transfer it to a desktop (cringe!) and use Excel (shudder!!) to format and print. In my defence, I would add that I developed the sheet (and this novel) on the 200LX (sweetness & light!) Before you print, you can, of course hide any columns and/or cells and/or rows that you don't want the recipient to see. I often do because it's easier than having to explain what -MN and -% etc. are for. Customising ----------- If you want to meddle with a thing of beauty, then you may have to turn off the global protection on calculated cells. Type [MENU] W G P D to disable it and [MENU] W G P E to turn it back on again afterwards. Also note that columns H & J are hidden. ---- Hope you like it. Chris R.