Applying Excel Macros to your timesheet data

Last updated - Mar 06, 2024 at 8:45AM

If you export your timesheet data to a file (Excel, CSV, TXT), you can create an Excel macro to automatically apply calculations, styles and formatting to your exported data.

An Excel macro records styles, formatting and forumlas in a spreadsheet, and saves it as a procedure to run on any future spreadsheets. That means that you only need to record writing your formulas and formatting once, and then you can simply run your saved macro to apply to your on your future timesheet export files.

Follow these steps to create an Excel macro:

  1. Download your timesheet file to payroll or custom CSV.
  2. Open the file in Excel. If the file doesn't automatically open in Excel, open a new Excel workbook, click on the Data tab in the top ribbon, then click From Text/CSV and follow the steps in the wizard. Excel import data from file
  3. Enable the Developer tab by following these instructions: https://support.microsoft.com/en-us/topic/show-the-developer-tab-e1192344-5e56-4d45-931b-e5fd9bea2d45
  4. Click on the Developer tab in the top ribbon, then click Record Macro. Excel record macro
  5. Give your macro a name, and ensure you choose to store the macro in Personal Macro Workbook. Click OK. Excel new macro
  6. Use Excel formulas to add any calculations (e.g. overtime), and apply any styles and formatting such as adding/renaming columns.
  7. Once you've finished applying your calculations and formatting to the spreadsheet, click on Stop Recording in the Developer tab. Excel stop recording macro

Follow these steps to run your saved macro on exported timesheets:

  1. Download your timesheet file to payroll or custom CSV. Note this file must be in the same format that your macro was recorded for.
  2. Open the file in Excel. If the file doesn't automatically open in Excel, open a new Excel workbook, click on the Data tab in the top ribbon, then click From Text/CSV and follow the steps in the wizard. Excel import data from file
  3. Click on the Developer tab in the top ribbon, then click Macros. Excel macros
  4. Ensure that you've chosen macros in All Open Workbooks, click on your macro name from the list, then click Run. Excel run macro
  5. Your calculations and formatting will be automatically applied to the spreadsheet data.

Example:

Exported data in Excel before running macro

Excel data before macro

Exported data in Excel after running macro

Excel data after macro

See also:

Custom CSV export
Overtime rules