Arrow-Tip #30 Using the Outlook Calendar to Record Mileage
Some of you have may have read Arrow-Tip # 13 Can Outlook Help Me Track Billable Hours which discusses using the Microsoft Outlook Calendar to track billable hours. Thanks to Jim Long of Verge New Media for inspiring me through a twitter post to write about how I also use the Outlook Calendar to track mileage.
As with billable hours, one of the advantages to tracking mileage on your Outlook Calendar is that you already have the appointment and location listed there. So tracking miles just requires a custom Calendar table view and then viola you can copy and paste your Outlook Calendar into an Excel spreadsheet, add a summation cell and your mileage is calculated. So here is how I do it.
Custom View
First I created a custom table view in the Outlook calendar folder. Generally I keep my Calendar in the “Work Week” view, however, when entering custom data that will be used for reporting I find a table view much more helpful. Once you have created a custom table view called “Mileage” or whatever name you would like to use, you will need to add some fields.
Mileage View Fields
Click the Fields button on the on the Custom View window (if the Custom View window is not still open from creating the view then go to View/Arrange By/Current View/Define Views to open the View Organizer window and then select the row with “Mileage” for view name in the “Views for folder ‘Calendar’” list box and then click the Modify button on the right) and the Show Fields window will open (see thumbnail below).
The first field you will want to add to your Mileage view is the “Mileage” field where you can record the number of miles between appointments. The Outlook appointment item does not have a field called “Mileage”, however, the Outlook mail item does. To access this field, select “All mail fields” in the “Select available fields from” drop down box in the top left corner of your Show Fields window and you should then see “Mileage” as one of the fields listed in the “Available Fields” list box below. Select Mileage in the “Available Fields” list box and then click the Add button - it should now appear in the “Show these fields in this order” list box on the right hand side of the Show Fields window. Use the Move Up and Move Down buttons below the “Show these fields in this order” list box to change the position of the Mileage field in the list box and view.
You may also like to add a field for “Company” to represent the company you are visiting. Once again, Outlook appointment items do not have a standard Company field, however, Outlook journal items do so you can add the Company field by selecting “All Journal Fields” in the “Select available fields from” drop down box just as you did with “Mileage” above.
Next you might want to add custom fields such as “Title” or “Reason for Visit” which many companies like to see on expense reports.
Excel Spreadsheet
Once you have got your table view set up, you can copy items from your Outlook view and then paste them into Excel where values can easily be summed or pasted directly into an expense report. Make sure when you populate the Mileage field that you use symbols that will allow Excel to properly interpret your text. For example, to capture mileage from one appointment to the next appointment in one item, enter an = sign as the first symbol in the mileage field and then the number of miles to the appointment destination followed by a + sign and then the number of miles back to the office e.g. “= 10 + 15.9″ (see thumbnail below).
Now simply select the rows you would like to export and then go to Edit/Copy to copy the Outlook fields and then in Excel go to Edit/Paste Special and the Paste Special window will open (see thumbnail below).
Make sure to select “Text” in the “As” list box and then click the OK button. Your Outlook appointment items should now be pasted into Excel (see thumbnail below). Note that if you used the = sign and + sign in your Mileage field in Outlook, Excel will calculate the addition for you when you paste your Outlook data.
Conclusions
Hopefully the Outlook Mileage view will come in handy for you whether you are required to submit an Expense Report and just want an easier solution for yourself or your assistant or you just need to track mileage for tax reporting purposes.
Comments
No comments yet.