[caption id="attachment_30653" align="aligncenter" width="315" caption="Worksheet Set Up"][/caption]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. [caption id="attachment_30654" align="aligncenter" width="284" caption="Home tab, Editing group, Clear, Clear Formats"][/caption] 4. In the Arrive through Depart columns, use Ctrl+: (Ctrl+Shift+;) to “punch in” and “punch out”. [caption id="attachment_30655" align="aligncenter" width="315" caption="Ctrl+Semicolon and Ctrl+Shift=Semicolon"][/caption] 5. In Total Time enter this formula: • =((D2-C2)+(F2-E2))*24 • 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. [caption id="attachment_30656" align="aligncenter" width="271" caption="Time Calculation Formulas"][/caption] 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.
Get weekly updates on breaking news and business advice to help you advance your career! The week's top stories and resources will be sent right to your inbox. Choose the topics you're interested in:
We value your Privacy.
Previous post: Heed the new cost basis rules