Calculate hours worked with Excel Timesheet Formulas – 4 steps:
STEP 1: Data Entry
On Column A: Enter the days of the week
On Column B: Enter the time work started, in an hh:mm format
On Column C Enter the time work ended, in an hh:mm format
You may use AM/PM or military time, but be consistent within your timesheet
8:54 AM (8 colon 54 space AM)
5:00 PM (5 colon 00 space PM)
STEP 2: Calculate hours worked
On column D:
Type the Excel Formula: =SUM(C2-B2)*24
Change the cell format to “Number” with 2 decimal places.
STEP 3: Calculate Pay
Cell H1 Type the hourly pay of that employee
On Column E: =SUM(D2*$H$1)
(The $-$ will prevent the cell number to roll over when you copy the formula down through the days of the week)
STEP 4: Drag down and finish
Drag the timesheet formulas of Column D & E through the days of the week
Cell D9: For total hours, use =SUM(D2:D8)
Cell D10: For total pay, use =SUM(E2:E8)
If you like this instruction, click the Facebook Like button or share it!
| Date | Log in | Log Out | Total Hours | Total Pay | |
| Monday | 9:15 AM | 5:02 PM | 7.78 | $77.83 | |
| Tuesday | 9:20 AM | 5:00 PM | 7.67 | $76.67 | |
| Wednesday | 0.00 | $0.00 | |||
| Thursday | 0.00 | $0.00 | |||
| Friday | 0.00 | $0.00 | |||
| Saturday | 0.00 | $0.00 | |||
| Sunday | 0.00 | $0.00 | |||
| TOTAL | 15.45 | $154.50 | |||
