Author:
William Ramirez
Date Of Creation:
22 September 2021
Update Date:
1 July 2024
Content
An Excel spreadsheet can be written to convert time values to integers for use in tasks such as calculating payroll, determining labor costs, and estimating productivity rates. Typically, a formula with an IF function is used to convert values to get the difference between regular and overtime hours. The resulting numbers are multiplied by the wage rate to get the total payments. See Step 1 to learn how time is calculated in Excel spreadsheets.
Steps
1 Enter the column headings. Type “In” in cell B1, “Out” in cell C1, again “In” in cell D1, and “Out” in cell E1. Type “Regular” in cell F1 and “OT” in cell G1. 
2 Enter the days of the week. Type "Mon" in cell A1. Click on the fill handle in the lower-right corner of cell A1 and drag the formatting down all the way to cell A8. Column A displays the abbreviations for the days of the week. 
3 Enter labels for weekly totals. Enter “Total Hours” in cell E9, “Hourly Rate” in cell E10 and “Total Pay” in cell E11. 
4 Maintain a formula for calculating the official hours. In cell F2, enter the following formula: = IF ((((C2-B2) + (E2-D2)) * 24)> 8.8, ((C2-B2) + (E2-D2)) * 24) ... Click on the fill handle in the lower-right corner of cell F2 and drag the formatting down to cell F8. Times entered in columns A through E will be converted to whole values and shown in column F. 
5 Enter the formula for calculating overtime hours. In cell G2, enter the following formula: = IF (((C2-B2) + (E2-D2)) * 24> 8, ((C2-B2) + (E2-D2)) * 24-8.0) ... Click on the fill handle in the lower-right corner of cell G2 and drag the formatting down to cell G8. Overtime values entered in Columns A through E will be converted to whole values and shown in Column G. 
6 Enter formulas to calculate total hours, official hours, overtime, official pay, overtime pay, and total pay. Enter the formula for calculating the official payments in cell F11 "= F10 * F9." Enter "= G9 * G10" in cell G11 to calculate overtime pay. - In cell F9, enter "= SUM (F2: F8)" to calculate the number of hours worked normally, and in G9, enter "= SUM (G2: G8)" to calculate the overtime hours. In cell G10, enter "= F10 * 1.5" to calculate the overtime pay rate. Enter "= SUM (F11: G11)" in cell H11 to calculate the total payments. All formulas have been entered into the Excel spreadsheet.
7 Format numeric values. Select cells in the range A2 to E8 and right-click on the selected cells. From the drop-down menu select "Cell Format" and click on the "Number" tab. From the Category menu on the left side of the dialog box, select Time and select 1:30 PM from the Type menu to the right of the Category menu. Click OK. Select cells F11 through H11 and click the Currency ($) button on the Number menu of the formatting. 
8 Format the fill and borders. Select cells A1 - H1. Press and hold the Ctrl key and select cells H1 - H10. Continue to hold down the Ctrl key and select cells A2 - A8 and A9 - E11. When all the cells are selected, click on the "Fill Color" button on the toolbar and select "Gray, White Background, 50%." Then click on the "Font Color" button and select white. - Select cells A2 - G8. Press and hold the Ctrl key and select cells F9 - G11. Click on the "Font Color" button and select "Automatic". Then click on the "Borders" button on the toolbar and select "All Borders." Formatting applied.
9 Test the time table for accuracy. Type "8:00" in cell B2, "11:00" in cell C2, and "12:00" in cell D2. Select cells B2 through D2 and use the fill handle in cell D2 to drag the values down to row 6. Use the fill handle in cell B2 to stretch the formatting to cell B8. - Type “6:00” in cell E2, “4:00” in cell E3, and “4:30” in cell E4. Copy the value from E2 and paste it into cells E5 and E6. Enter an hourly rate of $ 12 in cell F10. The total pay in cell H11 should be $ 530, the overtime pay in G11 should be $ 63, and the total pay in cell F11 should be $ 468. Check the formulas you entered again if you get a different result. Excel timesheet is ready.
