• LinkedIn
  • YouTube
  • Twitter
  • Facebook
  • Google+

Time Sheets and Shortcuts

by on
in The Office Tech Pro

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.
[caption id="attachment_30653" align="aligncenter" width="315" caption="Worksheet Set Up"]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"]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"]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"]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.

Like what you've read? ...Republish it and share great business tips!

Attention: Readers, Publishers, Editors, Bloggers, Media, Webmasters and more...

We believe great content should be read and passed around. After all, knowledge IS power. And good business can become great with the right information at their fingertips. If you'd like to share any of the insightful articles on BusinessManagementDaily.com, you may republish or syndicate it without charge.

The only thing we ask is that you keep the article exactly as it was written and formatted. You also need to include an attribution statement and link to the article.

" This information is proudly provided by Business Management Daily.com: http://www.businessmanagementdaily.com/30651/time-sheets-and-shortcuts "

Leave a Comment