Between the timesheets

Pro

Reader Vincent Mooney writes: “I am using Excel 2000 and wondered if you could help me calculate my timesheet. I work unconventional hours and I want to be able to put the times I started and finished work into a cell, enter my hourly rate and get an answer. I sometimes work past midnight and on Sundays, and I have problems calculating time and converting it to charges.”

Answer: Numerous timesheet templates are available but they rarely suit everyone’s circumstances. You gain a better understanding of Excel if you create your own template. The example given here won’t work for everybody, but it demonstrates the typical functions, formulas and formats necessary when you’re creating timesheets.

Open a new workbook and type in the labels shown in column A of screen 1. In cell B2 enter the date of the Sunday of the first week to be recorded. In B4 enter

=B2

In C4 enter

=BN4+1

Point to the lower-right corner of cell C4 and drag across to cell H4, which should then read

=G4+1

Highlight the range B4:H4 and give it the Custom format ddd mmm d.

Choose B2. Press Ctrl and highlight the range B5:H7, which selects this cell and range for formatting. On the Format menu choose Cells, Border, Outline. In the Style box choose a thick line. In the colour box choose red. When you click OK this will put a red line around these cells to remind you of those to be filled in each time.

On the Tools menu choose Options, View and uncheck Zero values. A timesheet looks neater if every blank cell doesn’t have a 0 in it.

Your hourly rates can remain confidential. They need not appear on the worksheet if given Names. Hold down the Ctrl key and press the F3 function key. This opens the Define Name dialogue box. In the Names in workbook: box enter Regular. If your regular hourly rate is EUR*16.00, enter =16 in the Refers to: box. In the same way, enter your hourly rates for Overtime and Sunday, as in screen 2. Save the workbook to record your progress so far.

Give the range B5:H6 the Custom format h:mm AM/PM. Using the 24-hour clock, enter some typical work periods. If you work eight hours starting at 4pm on the Monday you would enter 16:00 in cell C5, 24:00 in C6, 0:01 in D5, and 4:00 in D6. This is because you worked four hours on Monday and four hours on Tuesday. Enter other likely work periods in the remaining cells in the range B5:H6.

Times v totals of hours

Spreadsheets record time as fractions of a day. In Excel for Windows the days are counted from Jan 1 1900 and stored as a date number. You can see this number if you press Ctrl and the key to the left of 1 on the top row of the standard keyboard (`). Press this shortcut again to return. Alternatively, choose Options on the Tools menu, View and check the Formulas box.

During most work periods you will take breaks that are not charged to your clients. A record of such rest periods, your overtime hours, and your total hours are measurements of time, not particular historical times. So rather than store them using date numbers, it makes more sense to record them as a number of hours and quarter hours. Highlight the range B7:H9 and on the Format menu choose Cells, Number, Fraction. Enter in these cells some typical breaks. An hour is entered as 1. For an hour and a quarter, enter 1.25, using a full stop and not a colon. Enter an hour and half as 1.5. An hour and three-quarters is 1.75.

If you want to charge your time rounded to quarter hours you can use the MROUND function. On the Tools menu, under Add-Ins, make sure the AnalysisToolPak is checked. If it isn’t there, load it from your Office disc. In cell B8 enter

=MROUND(((B6 B5)*24),0.25) B7

Point to the lower-right corner of the cell and drag the formula to H8. The 24 in the formula converts decimal fractions of days into hours. The MROUND function has two arguments: a number and the multiple. Fortunately MROUND will accept a formula as the number

(B6-B5)*24

The multiple is 0.25 because you want your results rounded to quarter hours.

Assuming your overtime rate applies after eight hours, calculate your overtime hours by entering

=IF(C8>8,(C8-8),0)

in cell C9 and drag this formula to H9. It’s not needed in B9 as Sunday is already at an overtime rate. Save the workbook again as this completes the timesheet portion, see screen 3.

It is easy now to calculate your week’s earnings. Highlight B12:B15 and click the Currency tool. In cell B12 enter,

=B8*

then press the function key F3 to display the available Names and choose Sunday. Click OK and the entry will be

=B8*Sunday (see screen 4).

In cell C12 enter

=(C8-C9)*

then press F3 and choose Regular. Click OK and the entry will be

=(C8-C9)*Regular

Drag this formula across to H12. In C13 enter

=C9*Overtime

and drag this formula across to H13.

Highlight B14:H14 and click the Sum tool. In B15 enter

=SUM(B14:H14

to arrive at your week’s total earnings. On the Tools menu choose Options, View, Window options, and uncheck the Gridlines box (see screen 5). This helps convey the appearance of a timesheet rather than a spreadsheet. Save the final result.

Read More: Hardware & Equipment