Software

Office Q&A: An Outlook categories trick and a revamped Excel timesheet

This month, Susan Harkins helps readers expand two previous techniques. See how to assign categories to Outlook appointments for IMAP accounts and tweak a timesheet to evaluate double-time hours.

officeq-a-tr.jpg

Solutions are never finished. No matter how thorough and comprehensive you try to be, someone will need a tweak or two. This month, several readers asked about reclaiming categories for Outlook appointments after reading my article on reclaiming them for email messages. In addition, Tania liked an Excel timesheet she found in an older article, but she needed it to do a bit more. In this month's Q&A article, you'll learn how to assign categories to appointments in versions of Outlook that no longer (easily) support categories and how to overhaul a timesheet so it evaluates double-time.

I'm using Office 2016 on a Windows 10 64-bit system. Starting with Outlook 2010, Outlook defaults to IMAP accounts, and categories are missing—at least, you can't easily access them via the interface. If you're using a version earlier than 2010, you won't need this solution. The Excel timesheet should work in any version of Excel. For your convenience, you can download the demonstration .xlsx or .xls file.

Assigning categories to appointments in IMAP accounts

I receive a lot of mail from readers regarding Reclaim Outlook categories for IMAP accounts. Most often, they want to use the reclaimed categories with appointments—and the article focuses on email messages. It does offer the solution; readers just don't realize it. I apologize, because I should've clarified the connection in the original article. Once you assign a shortcut to a category, you can use that shortcut to assign a category to other Outlook items, not just email messages. It's not a silver bullet, but it works. The confusing part is that you must use the email message interface to assign the shortcut.

SEE: How to avoid and overcome presentation glitches (free PDF)

The first step is to assign a shortcut to a category. To do so, click New Email in the New group on the Home tab or press Ctrl+N. Then, click the Tags dialog launcher. In the resulting dialog, choose All Categories from the Categories dropdown shown in Figure A. Select a category such as Green Category. Then, choose a shortcut key combo from the Shortcut Key dropdown. As you can see in Figure B, I chose Ctrl+F2. Outlook displays the assignment in the dialog, as shown in Figure C. Click OK and then Close. You can close the email message now (or not). At this point, you can assign the green category to any email message by selecting that message and pressing Ctrl+F2.

Figure A

qamay2017a.jpg
Select All Categories to access the option to assign a keyboard shortcut to a category.

Figure B

qamay2017b.jpg
Choose a shortcut key combo.

Figure C

qamay2017c.jpg
Outlook displays the new shortcut in the dialog.

You might be wondering how this helps with appointments. Once you assign a shortcut key to the category, you can use that shortcut with appointments and other items, not only email messages. Simply select an appointment (in calendar view) and press Ctrl+F2 to assign that category to the selected appointment. Figure D shows the resulting category applied to the Herb Series appointment. It can take a little time to assign shortcuts to all the categories you want to use, but if you want the power of categories, it's worth the effort.

Figure D

qamay2017d.jpg
The Herb Series appointment has a green category assignment.

SEE: How to translate emails into languages you can read with the free Translator for Outlook

Double time: Nice work if you can get it

After reading Build a simple timesheet in Excel, Tania asked for a formula to figure double time after the first 12 hours worked. Specifically, Tania needs to satisfy the following business rules:

  • First eight hours worked is straight pay.
  • Hours nine through 12 are time and a half.
  • Anything over 12 hours is double time.

Before we modify the original timesheet, I want to recommend that you consider a template. There are plenty of them out there and many are free. It's often easier to tweak a template than to build something from scratch. Fortunately, this timesheet is simple and won't require too much of your time.

Now, to modify the timesheet shown in Figure E so it evaluates double time, you must first insert a column to the left of the sick column (column I) by selecting column I, right-clicking the selection, and choosing Insert from the submenu. In I7, enter the following expression and copy it to I8:I13:

=IF(((D7-C7)+(F7-E7))*24>12,((D7-C7)+(F7-E7))*24-12,0)

Figure E

qamay2017e.jpg
This simple timesheet tracks regular work hours and overtime.

Figure F shows the expression at work after changing a few time values. Similar to the expressions in columns G and H, the double-time expression evaluates the in and out times to determine the hours worked, but it handles those results differently. Specifically, if the time worked is greater than 12, the double-time expression returns the number of hours over 12. When the total is less than 12, the expression returns 0.

Figure F

qamay2017f.jpg

The expression works fine, but it breaks the overtime and total expressions. First, the overtime expression returns the total overtime hours, without subtracting the double-time hours. To fix that, enter the following expression into HI and copy it to the H8:H13:

=IF(((D7-C7)+(F7-E7))*24>8,((D7-C7)+(F7-E7))*24-I7-8,0)

The new overtime expression returns the number of hours worked between eight and 12. Consequently, the daily totals in column L now reflect the change in the overtime values. We still need to complete the timesheet by adding a weekly total to the double-time column. To do so, select I14 and click AutoSum in the Editing group. With just these few changes, the timesheet shown in Figure G now fits Tania's requirements.

Figure G

qamay2017g.jpg
The timesheet now fulfills Tania's business rule that requires double-time.

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.

Also read....

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox