Time Sheets and Shortcuts — Business Management Daily: Free Reports on Human Resources, Employment Law, Office Management, Office Communication, Office Technology and Small Business Tax Business Management Daily
Rachelle asks: “I would like to set up excel spread sheet to track my hours for work but can’t figure out how to set it up, can someone please help!”
There are several great templates on Office Online which you can access with File (or Office Button) New and looking for templates on Office Online. Here’s a cool little “do-it-yourselfer” that uses the shortcuts Ctrl+; (semicolon) and Ctrl+: (Shift-semicolon) to punch in and out.
Worksheet Set Up
1. Set up 7 columns: Employee (or project), Date, Arrive, Lunch Out, Lunch In, Depart, Total Time.
2. In the Date column, click Ctrl+; to enter the current date.
3. Click on the column for Total Time and use the Clear button to access Clear Formats.
Home tab, Editing group, Clear, Clear Formats
4. In the Arrive through Depart columns, use Ctrl+: (Ctrl+Shift+;) to “punch in” and “punch out”.
Ctrl+Semicolon and Ctrl+Shift=Semicolon
5. In Total Time enter this formula:
• Where D2 is Lunch Out, C2 is Arrival, F2 is Depart and E2 is Lunch In
6. If you don’t take lunch all the time, try this formula which takes that into consideration.
• =IF(AND(E3="",D3=""),(F3-C3)*24,IF(OR(D3="",E3=""),"complete lunch time in/out",((D3-C3)+(F3-E3))*24))
• This formula checks to see if Lunch In and Lunch Out are both blank. If so, it just calculates total time with Arrive and Depart. If only one of the lunch fields is filled out, this formula alerts you to “complete lunch time in/out”.
• So, if you were to leave at lunch, you’d Ctrl+: in the Depart column.
Time Calculation Formulas
Something you might notice is that all time calculations involve “*24.” That’s because when you do math with time it leaves a number equal to the fraction (as a decimal) of a 24 hour day.