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 on your future timesheet export files.
Follow these steps to create an Excel macro:
Download your timesheet file to payroll or custom CSV.
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.
Enable the Developer tab by following these instructions: https://support.microsoft.com/en-us/topic/show-the-developer-tab-e1192344-5e56-4d45-931b-e5fd9bea2d45
Click on the Developer tab in the top ribbon, then click Record Macro.
Give your macro a name, and ensure you choose to store the macro in Personal Macro Workbook. Click OK.
Use Excel formulas to add any calculations (e.g. overtime), and apply any styles and formatting such as adding/renaming columns.
Once you've finished applying your calculations and formatting to the spreadsheet, click on Stop Recording in the Developer tab.
Follow these steps to run your saved macro on exported timesheets:
Download your timesheet file to payroll or custom CSV. Note this file must be in the same format that your macro was recorded for.
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.
Click on the Developer tab in the top ribbon, then click Macros.
Ensure that you've chosen macros in All Open Workbooks, click on your macro name from the list, then click Run.
Your calculations and formatting will be automatically applied to the spreadsheet data.
Example:
Exported data in Excel before running macro
Exported data in Excel after running macro
