How to Make a Time Card in Excel

by Ken Burnside

Excel's time and date functions make it a useful tool for office management purposes. One of the most basic functions is making an employee time card that tracks when the employee came in for the shift, when he left for breaks or lunch and when he clocked out for the day.

1

Enter the following column headers in Row 1: Employee Name, Employee ID, Shift Start, Break1 Start, Break1 End, Lunch Start, Lunch End, Break2 Start, Break2 End, Shift End, Total Break, Total Hours.

2

Select cells C2 through L2. Right click and select "Format," then select "Cells." In the type of format, choose "Time." This ensures that Excel will read the values as times.

3

Select cells C2 through J2, then select the "Data" tab. Choose "Data Validation," and in the dialog box that comes up, choose "Time," leaving the drop down at "Between." For the start time, put 00:00 and for the end time put 23:59. Click on "Apply." This restricts the way data can be entered into these cells to times in a 24-hour clock.

4

Enter the following formula into cell K2: " =SUM(E2-D2,G2-F2,I2-H2)." This calculates the cumulative duration of all the breaks the employee takes.

5

Enter the following formula into cell L2: " =MOD(J2-C2,1)-K2." This calculates the duration of the shift minus the duration of the breaks. The =MOD() function ensures that the correct duration is noted for shifts that span over midnight.

About the Author

Ken Burnside has been writing freelance since 1990, contributing to publications as diverse as "Pyramid" and "Training & Simulations Journal." A Microsoft MVP in Excel, he holds a Bachelor of Arts in English from the University of Alaska. He won the Origins Award for Attack Vector: Tactical, a board game about space combat.

Photo Credits

  • photo_camera Siri Stafford/Digital Vision/Getty Images