If you need to perform a simple conversion from hours to decimals, you can use Microsoft Excel; enter the time in "hh:mm" form, and multiply by 24, converting, say, three hours and 30 minutes ("3:30") into the decimal "3.5." But a more complex situation arises when you need to convert clock time into tenths of an hour, or six-minute units, a common requirement in law offices. An employee first rounds his time to nearest billing unit, so that a five-minute task becomes six minutes, then uses a chart to convert the six minutes into one-tenth of an hour, for a timesheet entry of "0.1." Using Excel 2007, you can automate the process and make timesheets easier.
- Skill level:
Other People Are Reading
Open Microsoft Excel 2007.
Build a conversion table. In cell A4, type the header "Minutes." Below the header, working downwards from cell A5 through cell A16, enter numbers in multiples of five, starting with 0 and ending with 55.
In cell B4, type "Timesheet." Underneath, in cells B5 through B16, enter the following values:
Cell B5: 0 Cell B6: 0.1 Cell B7: 0.2 Cell B8: 0.3 Cell B9: 0.3 Cell B10: 0.4 Cell B11: 0.5 Cell B12: 0.6 Cell B13: 0.7 Cell B14: 0.7 Cell B15: 0.8 Cell B16: 0.9
Name the range. Select the conversion table, not including column headers (A5:B16). In the Name box (to the left of the formula bar), type "ConversionTable." Press "Enter."
Create headers. In cell A1, type "Enter Start Time." In cell B1, type "Enter End Time." In cell C1, type "Total Time." In cell D1, type "Enter on Timesheet."
Enter sample times. In cell A2, type a start time, using "hh:mm" time format and including "AM" or "PM." For example, type "3:05 PM." In cell B2, type an end time, such as "3:45 PM."
Calculate total hours. In cell C2, type the following formula:
Press "Enter." Right-click the cell and select "Format Cells." Under the "Time" category, select the last time format, "37:30:55." Click "OK."
For the sample times, cell C2 will display "0:40:00" (40 minutes).
Find decimal time for timesheet entry. In cell D2, type the following formula:
Press "Enter." Right-click the cell and select "Format Cells." Under the "Number" category, change the number of decimal places to 1. Click "OK."
For the sample times, cell D2 will display "0.7."
Save the worksheet. To calculate times, enter start time and end time. The total hours and the decimal timesheet entry will be calculated automatically.
Tips and warnings
- To make sure your formulas are not accidentally deleted, protect the worksheet. From the ribbon's "Review" tab, in the "Changes" group, click "Allow Users to Edit Ranges." Click "New." Under "Refers to cells," browse to A2:B2. Click "OK." At the bottom of the dialogue box, click "Protect Sheet." Type a password, if desired, and click "OK."
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for