Hands on: Calculate hours and pay on an Excel spreadsheet

Calculations of time are among the most frequent uses of Excel, and the most confusing.

You might accept that a spreadsheet stores a decimal fraction of a day as a record of time and displays hours and minutes like a digital clock but still be unable to get Excel to do what you want.

So it’s worth going over a few examples, starting with a record of a day’s labour costs.

The staff members’ names are in column A, start times in B, finish times in C, total hours worked that day in D, the particular staff member’s hourly rate in E, and the total labour cost for the staff member in F.

Highlight B2:C12. Right-click and choose Format Cells, Custom and in the Type: box enter

h:mm AM/PM

Highlight D2:D12 and use the Number format with two decimals. Format E2:F12 as Accounting with two decimals.

For staff member Art, enter 9:00 in cell B2 and 17:00 (which is 5pm in 24-hour time) in C2. In D2 enter

=(C2-B2)*24

To see why, click on B2 and press Ctrl and ` (the key to the left of 1 on the top row of the keyboard). You’ll see that the number stored in B2 is 0.375. That’s because time is stored in spreadsheets as a decimal fraction of a day. Multiply 0.375 times 24 and the answer is 9 or 9am. The number stored in C2 is 0.70833 recurring. Multiply that by 24 and the answer is 17, representing 5pm.

The entries in B2:C12 are in hours and minutes. The formulas in column D convert the difference between the entries in columns B and C to hours and decimal fractions of an hour. Someone who has worked from 11am to 5:15pm has worked 6.25 hours.

Complete the entries in A2:C12 and drag the formula D2 down column D. From here it’s easy. Enter the hourly rates in column E. In cell F2 enter =D2*E2 and drag this formula down column F. Column F now tells us the day’s labour costs for all staff members.

An individual timesheet
Let’s move on to something a little more complex, the timesheet for a self-employed person who works odd hours, often at night. Type in the labels shown in column A in screen 2. In cell B2 enter the date of the Sunday of the week to be recorded. Highlight the range B4:H4 and give it the Custom format

ddd mmm d

In B4 enter =B2. In C4 enter B4+1. Point to the lower right of the cell and drag along to H4 which should read, in the Formula Bar, G4+1.

On the Tools menu, choose Options, View and remove the check from the Zero values box. In Excel 2007, choose Office, Excel Options, Advanced, Display options for this worksheet, and remove the check against ‘Show a zero in cells that have zero value’. Click OK. A timesheet looks neater if blank cells don’t contain zeros.

Hourly rates can remain confidential if they don’t appear on the worksheet. Put them in Names instead. Press Ctrl & F3. In the ‘Names in workbook’ box enter Regular. In the ‘Refers to:’ box enter 16 if you want to make the regular hourly rate £16.00. Similarly Name hourly rates for Overtime and Sunday.

Like this story? Spread the news by clicking below:

 del.icio.us     Digg this     reddit!