Need to pull timesheet data into an Excel spreadsheet? Want executive spreadsheets? Special reports? Payroll info in Excel?

Watch the video below. You'll find exactly what you need. Then scroll down for more info.

XLST is an Excel Add-in that pulls time and expenses from your timesheet and puts them into a spreadsheet of your design.

 

 

 

XLST is an add-in. That means it plugs into Excel, and provides it's own menu and ribbon. You'll know you have XLST installed when you see the new menu at the top of the Excel window.

 

So... XLST is installed... what do I do now?

Start with the XLST-Test.xlsx spreadsheet found in the XLST installation folder. That spreadsheet contains all the Excel functions supported by the add-in. It has several worksheet tabs at the bottom, where you will see examples of every Excel function. You'll see context and descriptions that help explain how each function is used. You can connect this spreadsheet directly to your own SQL database.

 

Connecting XLST to the ST database

Before you can use any Excel functions from XLST, you must connect to your ST timesheet database. Start by clicking the XLST menu, and then the Standard Time® icon. A connection dialog will appear. You must have SQL Server (or SQL Express) authentication to connect. That means ST must be connected to SQL. You may have to start by connecting ST to SQL before XLST will be of any use. XLST does not connect to MS Access databases. Once XLST is connected to SQL, you will never have to do it again on that machine, for that windows login. It is a one-time operation.

 

Inserting an Excel function

The next step is to insert a new XLST Excel function into an empty cell. Just click on any empty cell in your spreadsheet, and then click the little function icon next to the formula bar in Excel. A new dialog will appear. Choose XLST from the Category dropdown. Now you'll see all the XLST functions available to you. Click one, and a new dialog will appear. It is asking for parameters for the chosen Excel function.

 

What are Excel function parameters?

Every XLST function needs parameters to operate. In other words, XLST functions will not produce an output until you specify the information you want from them. You do that with parameters. Each parameter instructs the function to find data for the choices you make. For example, if you choose employee "Buzz Aldrin" as a parameter, the function will find information for that employee. Or, if you choose "Neil Armstrong" as a parameter, you will get information for him. The choices you make determine the information you get back from the function. These choices are called parameters.

 

Are all parameters required?

There are a lot of parameter choices for each XLST function. Are they all required? Usually not.

Most functions require only a few parameters to operate. They will attempt to return something with the least amount of information. But they all require a few parameters to return something. The more parameters you supply, the more detailed the information will be.

For example, if you choose "Buzz Aldrin" on project "Apollo CSM-107" you would get different information than "Buzz Aldrin" on project "Apollo LM-5". Each parameter affects the outcome.

 

What can I do with the XLST Excel Add-in?

XLST is an executive and backoffice tool. It is usually not client-facing, and usually not used by employees. In other words, it is used by administrative, management, and executive teams to collect and analyze employee timesheet data. Employees have already entered that data into their timesheets. You are now mining that information for company management purposes.

What purposes, specifically?

Payroll, financial reports, project management reports, project execution reports, client costs, salary costs, time and materials reports.

 

What functions are available in XLST?

Here is a partial list, and a description of each function.

1. XLSTActualWork, returns all employee hours in the timesheet over a date range
2. XLSTActualSalaryCost, returns salary costs for timesheet entries
3. XLSTActualClientCost, gets client costs for employee timesheet entries
4. XLSTExpenseTotal, gets total employee expenses over a date range
5. XLSTTimeOffHours, finds the total number of hours taken off by an employee
6. XLSTTimeOffAvailable, finds the number of hours available to an employee for PTO
7. XLSTScheduledHours, gets the number of hours an employee is scheduled to work
8. XLSTExceedDailySchedule, returns the number of hours exceeding the daily schedule
9. XLSTExceedWeeklySchedule, number of timesheet hours above the weekly schedule
10. XLSTProjectTaskScheduledHours, returns the number of hours scheduled for an employee
11. XLSTProjectTaskDuration, gets the duration in hours for a given task
12. XLSTProjectTaskActualWork, actual work that has been performed by employees
13. XLSTProjectTaskPercentComplete, the percentage of completion for a given task
14. XLSTProjectTaskModified, when a task was modified last
15. XLSTPayPeriodStart, finds the starting date of a given pay period
16. XLSTPayPeriodEnd, the ending date of a pay period
17. XLSTEmployeeTimesheetApproved, returns an indicator if an employee's timesheet is approved
18. XLSTEmployeeTimesheetSubmitted, if the employee submitted their timesheet
19. XLSTEmployeeExpensesApproved, if employee expenses are approved
20. XLSTProjectClientRate, returns the billing rate for a given project
21. XLSTProjectSalaryRate, the salary rate paid for a project
22. XLSTEmployeeClientRate, the billing rate charged for work by an employee
23. XLSTEmployeeSalaryRate, the salary rate paid for an employee
24. XLSTCategoryClientRate, the billing rate charged for a category
25. XLSTCategorySalaryRate, the salary rate paid for a category
26. XLSTProjectManager, gets the project manager assigned to a project

 

 

Back to Videos

 

More videos on YouTube