importing, TimeControl timesheet import

Importing legacy timesheet data into TimeControl

Leave a comment

Some organizations have had timesheets for years and, when they implement TimeControl, they ask if they can import legacy timesheet data from their older systems into their new timesheet software.

The answer is yes. TimeControl is designed with an Import Timesheet feature that is described in the Reference guide. Unlike the imports for all the TimeControl tables, the Import Timesheet function is a fixed format of import. Several fields are required in a particular order. The TimeControl Import Timesheet function takes a Comma Separated Values (CSV) file which can be created in Excel to bring in the data.

The fields required are:

Field as described in the import tool

Database Field name

Example Value
Week Ending Date

TSH_WEDATE

20080321
Timesheet Header Type

TSH_TYPE

NORM
Timesheet Header Employee

EMP_CODE

GJ018
Timesheet Header Owner

USR_CODE

ADMIN
Project Name

TSD_PRJ_NAME

DATA
Charge Code

TSD_CHR_CD

Data.2.3
Rate Code

TSD_RAT_CD

STANDARD
Hours Monday

TSD_CMON

8
Hours Tuesday

TSD_CTUE

8
Hours Wednesday

TSD_CWED

8
Hours Thursday

TSD_CTHU

8
Hours Friday

TSD_CFRI

8
Hours Saturday

TSD_CSAT

0
Hours Sunday

TSD_CSUN

0

There are a few constraints to be aware of:

  • First of all each of the values that are being used to create new Timesheet Headers and Timesheet Details must already exist in the system. So if you are creating a timesheet for Employee code GJ018 then that employee code must already be in the Employee table. Same goes for the User, the Rate code, the Project name and the Charge code.
  • “NORM” is a normal timesheet. That’s distinct from a DBCR (Debit/Credit) entry that TimeControl might store internally.
  • The ANSI date type is year/month/day in the format 20080321 which is March 21, 2008.
  • Once the import is complete, you’ll need to check the log to make sure everything you were trying to import arrived.
  • The timesheets, once created, will be in draft status and owned by the “Timesheet Header Owner” listed in the import record. In the example above that’s user “ADMIN”.

For those who are keen to get started, the HMS Technical Support department (Thanks James!) has made a spreadsheet available as a template. To use the spreadsheet, first download the Excel file called tcimporttimesheets.xlsx from the TimeControl website. You’ll find the same data as is displayed in the table above. Enter your data into the appropriate columns then delete the first column and the first three rows which include descriptive header data and the one example record that is listed above.

Happy importing!

Free TimeControl Training, Online Training, TimeControl Timesheet Software

All-new TimeControl Online Training Center

Leave a comment

It seems we’ve been talking about it forever, but over the holidays we have finally launched the TimeControl Online Training Center. This mini-training area lets users or prospective users see how to perform TimeControl functions through mini-lessons that are only 3 to 7 minutes long. Best of all, access to the TimeControl Online Training Center is absolutely free.

The Training Center includes a number of basic and advanced lessons to cover basic functionality. Lesson include:

  • Entering a timesheet
  • Adding a new user
  • Running TimeControl reports
  • Linking to a project management system
  • Adding a project
  • and more…

The TimeControl Training Center will be of interest to those using the TimeControl Hosted Evaluation site as well as being a good resource for existing users. Videos are displayed through a web browser using Flash technology. The data in the TimeControl Training Center lessons is the same as that found in the Hosted Evaluation site as well as the Hosted Trial sites that are made available from time to time to prospective users who are evaluating TimeControl.

Stop by the new training center at www.timecontrol.com/resources/lessons.

autofill, hr timesheets, human resources, time and attendance, timecontrol

TimeControl’s new Timesheet AutoFill

Leave a comment

TimeControl 5.1.5 has a new feature that we think will be a hit with Human Resources Departments. We all know about TimeControl’s strength as a task-based timesheet system and its links to project management but not all personnel in an organization are project personnel. Many organizations use TimeControl to track the entire staff including those employees who only need their time and attendance tracked. For these users we are often asked if timesheets can be added only “by exception”.

By exception timesheet entry means entering a timesheet only when your weekly timesheet won’t be a complete week of working as per your salaried schedule. So, if you have vacation, sick leave, personal time off or some other exception, you’ll enter only those hours. But what about the rest of the hours that make up your complete week? That’s what AutoFill is for.
TimeControl’s Timesheet AutoFill allows an administrator to create or fill-up timesheets for a selected group of employees. It’s not for all staff. The project personnel should be filling out their timesheets with all their hours per task. Timesheet AutoFill is for those people who only need to track time and attendance.

The Timesheet AutoFill command is in the Tools menu and, when selected, lets the Administrator decide whether to Create timesheets, add to existing unposted timesheets or to already posted timesheets. The Administrator uses the options that are appropriate to their timesheet process.

Then they can determine what to fill the timesheet with, a preset list, a list that comes only from the Employee defaults or a list that comes from Employee defaults when available and Administrator selections when not. Finally, the Administrator chooses which group or filtered group of employees to include in the AutoFill and starts the process. A log identifies exactly what timesheet data was added.

We’re conscious of TimeControl’s auditability so, we have, of course identified the hours that have been entered through AutoFill in both the Timesheet Detail and Posted Lines tables.

If you’d like to see the new function in action, take a look at the TimeControl Timesheet AutoFill webcast.

rate codes, Techniques, Tips and tricks

Project Specific Rate Codes

Leave a comment

Rate management is one of TimeControl’s most popular capabilities. Each hour worked on the timesheet can be calculated against a rate value according to the rate table. The end user selects a rate code on the timesheet and as the timesheet is posted, TimeControl calculates the costs.

Each rate code has two values, which are most commonly use for the internal or direct cost of the employee and the external or billable cost. TimeControl supports an unlimited number of codes which allows for many combinations. One of the most common requests is to track project specific costs.

Because there are an unlimited number of charge codes possible for each employee, the amount calculated for each hour of work can be different for each project and for each employee. For a client with a lot of projects, the number of possible rate codes that could be chosen by an employee, could become quite extensive. TimeControl provides a function that minimises the effort required to choose the correct rate code and that increases the accuracy of the entry. This function allows a project- specific rate code to appear when the project is chosen. This rate code will take precedence over any employee defaults that have been specified in the Employee table. To use this configuration, first add the project-specific rate code to the Labour Rate Table as a Global ate code type. This is a requirement for using the project-specific rate function. If you desire an employee-specific project costs, then add a rate with the same rate code value but as an employee specific rate. TimeControl will always select the most specific rate code applicable with that rate code value. So, for example, if the rate table has a rate code called “Std” for Standard rate and that is a Global Rate of $50 and a rate code called “Std” which is an employee specific rate for John, then when John does his timesheet, TimeControl will assume that the “Std” for John is the correct code.

Once the Global rate has been created, then go to the Project Table, and locate the project for which you’d like this rate code to be specified. On the Project Details tab select the project specific rate code.

Note that only global rates can be selected.
Selecting the “required” button will ensure that only this rate can be selected on a timesheet when this project is selected.

When the project is added as a line item to an employee’s timesheet the project specific default rate code will be automatically selected. If a matching employee rate type has been created, the employee rate value will be calculated. The project rate code will take precedence over the default rate code specified in the Employee Table.

In this example, the P_SPEC rate was added automatically to the DA Project line item.

exception timesheets, microsoft project, microsoft project server, Office 2010, Project 2010, project timesheets, timecontrol

TimeControl and Project 2010

Leave a comment

As you may have seen over on my EPM Guidance blog, Microsoft has now shown Project 2010 and Project Server 2010 at its Project Conference in Phoenix last month. HMS was in attendance at both the Project Conference and the Ignite Airlife immediately preceeding the conference. HMS is one of the longest standing partners of Microsoft Project. Our link between TimeControl and MS Project dates back to 1995 and we’ve maintained a continuous relationship with Microsoft since then. With my participation for five years on Microsoft’s EPM Partner Advisory Council, I’d already seen much of what Microsoft had intended in both Project and Project Server and, while nothing we saw in Phoenix was a big surprise for me, it was nice to see the version I’d spent so much time on coming close to fruition.

Some people have asked us already what the future of TimeControl and Microsoft Project will be and since the announcement of Project and Project Server 2010 is quite fresh, it’s a good time to make sure our existing and prospective clients are up to date.
First of all, TimeControl will have a link to both Project Server 2010 and Project desktop (meaning Project Standard and Project Professional) 2010. We will be releasing updates to both the TimeControl 5 and TimeControl 6 families closely following the release to market (RTM) of Microsoft of Project 2010. Microsoft will be releasing Project and Project Server 2010 simultaneously along with the rest of Office 2010 in “the first half of 2010”. Our release will happen shortly thereafter. HMS already has access to the “Technical Preview” version of Project and Project Server so we’ve been able to work on the link since late in the summer.
If you’re wondering if TimeControl’s relevance will change with new functionality in Project Server 2010, the answer is no. TimeControl’s position as a full timesheet application will be just as critical as it is now. The timesheet in Project Server 2010 has definitely improved and we think users will like it but as soon as you consider using a timesheet for both the updates to Project and for use as a financial system for example for payroll, the functionality of TimeControl for things like payroll rules, multiple rates, rate tables and more become essential. Plus, remember that TimeControl can link directly to Project Standard and Professional. We’ll be publishing an updated factsheet on TimeControl closer to the release of Project 2010.
One of the things that users of the new link will enjoy will be a huge improvement of performance on the TimeControl to Project Server link. Microsoft has updated the code in their PSI module that will make moving timesheet and task update data back to Project Server way easier than it’s been so far.
Stay tuned in the new year as there will be a number of announcements on the new link.
exception timesheets, hr timesheets, payroll timesheets, pto timesheet, timecontrol, timesheet software

Using TimeControl by exception

Leave a comment

In some organizations there is a great need to track what employees are doing with their day. A timesheet allows each employee to choose from a list of tasks and identify how much time each task took. TimeControl is ideally suited to such a system given it’s ability to track time task by task.

But there is another type of user that is often more plentiful than the project-based personnel. This user may not have a need track their tasks throughout the day. Perhaps the user does the same thing each day or perhaps the user is compensated and measured through other means.

Let’s take an high-tech organization. There may be researchers and IT specialists and marketers who work on projects throughout the week and we know we can be more effective if we can determine where their day is being used. But such an organization might also have salespeople. These people are compensated by commissions perhaps. Should they too need to complete a timesheet every day? (Actually there’s a good argument for doing just that but let’s imagine that in this organization we don’t wish to do this at all.)

TimeControl can also be used as a by-exception system. Some users could be asked to enter their timesheets on a weekly basis and the TimeControl validation rules can be set for those users to ensure that the timesheet represents a complete week of 40 (or some other number) of hours for example.

For other users however, TimeControl could be configured so that the users only enter a timesheet when they have taken time off. This “exception” time could be for vacation, sick leave, jury duty or just PTO – Personal Time Off. The possible entries for these users would only be for exception time and these users would not be listed in missing timesheet reports as timesheets would not be expected every week.

There are two ways this can be configured in TimeControl for the non project personnel. Option 1 would see the exception timesheets only containing the exception hours. Option 2 requires a few more hours work during configuration and adds a process which fills in additional hours to any exception timesheet or any blank timesheet to result in a complete list of hours for the week. Either option works well though reporting with Option 2 can be blended much more easily with the timesheet data of project personnel.

The benefit of mixing project and non-project users in a single TimeControl instance would ensure that 100% of staff were represented in the system. TimeControl’s reporting can be segregated with filtering to ensure that project and non-project timesheet data isn’t mixed or the data can be blended together as desired.

TimeControl’s flexibility makes it one of the few systems on the market that can accommodate both project and non project personnel from within the same system.

Assignment Filters, best practices, PM Links, timecontrol, Tips and tricks

TimeControl charges restricted by assignment

Leave a comment

By default, TimeControl allows users to see every possible project and every possible charge code in the system. There are, however, a number of features within TimeControl to restrict what projects and what charges each employee should be allowed to choose from.

In the TimeControl Employee Table, the administrator can declare a pre-defined filter to apply to the project selection and charge selection that will be applicable to each employee as they enter their timesheet. Popular filters include “only projects which are open” and “only charges which are open” or “only charges with a start date within 2 weeks of today”. These kinds of filters are relatively easy to create.

There is another request that we hear from time to time from those with experience in popular project management systems. These systems often constrain the timesheet to only accept charges from the employee’s assignments. This makes more sense when project resource scheduling is being done at the individual level which may not be the case for many environments. But, where individual assignments are identified, these project systems will present or show only those records for which the employee has a valid assignment.

Can TimeControl provide the same kind of functionality?
It can.

First, when TimeControl is linked to a project management system, we provide the option to bring in all the resource assignments. It’s not a requirement but when the option is selected, a table is loaded which is attached to the Resource Table. This data is called the “Preloading list” and shows all resources attached to a task back in the project system. Within TimeControl we can add or remove from this list manually. The list can be used for pre-loading but we can also take advantage of the list to make a filter that can constrain both the projects and charge codes that can be seen by each employee.

Project Filter
First, let’s make the filter for the Project List. Add a new filter, give it a name like “Project Assignments only” and select the Project table. The filter we want to make can’t be created from the standard field menu so clear the “Always generate the SQL when saving” option and click on the SQL tab. Now copy and paste in the follow SQL command:
SELECT TCPROJ.PRJ_KEY FROM TCPROJ WHERE ((PRJ_NAME = ‘ADMIN’) OR (PRJ_KEY IN (SELECT DISTINCT CHR_PRJ FROM CHARGE WHERE CHR_KEY IN (SELECT DISTINCT CRS_CHR FROM CHRRES WHERE CRS_RES = (SELECT EMP_RES FROM EMPLOYEE WHERE EMP_KEY=%TSH_EMP%)))) AND TCPROJ.PRJ_STATUS <> ‘Closed’)
Save the filter, go to the Employee table and select this filter in the Project Filter field.

Charge Restrictions
Ok, now here’s the Charge filter. Add a new filter, give it a name like “Charge Assignments only” and select the Charge table. Once again the filter we want to make can’t be created from the standard field menu so clear the “Always generate the SQL when saving” option and click on the SQL tab. Now copy and paste in the follow SQL command:
SELECT TCPROJ.PRJ_KEY FROM TCPROJ WHERE ((PRJ_NAME = ‘ADMIN’) OR (PRJ_KEY IN (SELECT DISTINCT CHR_PRJ FROM CHARGE WHERE CHR_KEY IN (SELECT DISTINCT CRS_CHR FROM CHRRES WHERE CRS_RES = (SELECT EMP_RES FROM EMPLOYEE WHERE EMP_KEY=%TSH_EMP%)))) AND TCPROJ.PRJ_STATUS <> ‘Closed’)
Save the filter, go to the Employee table and select this filter in the Charge Filter field.

Before you scramble off to make this the default for all your users, you’ll want to consider a few questions:
How often do people do work on activities to which they were not originally assigned? How good was the original plan? If people typically work on activities to which they weren’t assigned, you’ll need to do a lot more updating of the Preloaded list or linking to an updated project schedule with assignments in order for users to be able to complete their timesheet.

Is there an administrator available that can update the preloaded list as required? They’ll need to be available for sure near the end of the work week to quickly update possible entries.

Does the work and procedures that are necessary to keep this list maintained, add more or less work than the alternatives? It’s important to think of the ROI. Clients often ask for this option with the thought that it will reduce inaccurate data. If, however, there is so much effort maintaining the assignment list accurately, it may outweigh the benefits.

Frustration of users may be high if they can’t find their charge codes, resulting in bad data. If a user can’t find the right charge and can’t reach the administrator to make sure it’s in their assignments, then they might choose any charge in order to complete their timesheet. If that happens, Administrators may spend more time reassigning charged time through Debit/Credits to fix bad data.

On the other hand, if assignments are generally quite stable then limiting choices in this way may reduce user confusion in choosing the right charges.

Remember too that if the data is in the pre-loading table either through manual input or automatically by linking to a project management system and transferring the assignments, then the timesheet can be pre-loaded with the correct assignments when it’s added. That may be enough to encourage correct data without having to restrict it to absolutely these charges.

Think about the pro’s and con’s and test out the new filters with a small number of users before deciding to adopt it wholesale.

missing timesheet, timecards, timecontrol, timesheet administrator, timesheet systems

The odd case of the missing timesheet

Leave a comment

It’s the toughest thing about timesheet management, finding the missing timesheets on Monday morning (or whatever morning is your start of week). Fortunately TimeControl has functionality that lets you track down these timesheets that are yet to be.

There are two main weapons in the arsenal of TimeControl timesheet administrators to track down missing timesheets. The first is aptly named “The Missing Timesheet report”. This report has a number of options. When you start the report from the Reports Menu area, it asks whether to include:

        1. Only timesheets that have not been started

        2. Timesheets which have been submitted but have not yet arrived in “Posting” or completed status.
        Administrators can then produce a report showing any timesheets which stand between them and 100% compliance.

        As great as that sounds, there’s more. In the User Profiles definition, you can set security to show “Timesheet Data only for me and those who report to me.” This will ensure that the Missing Timesheet report only includes information from those below the person with this profile in the approval hierarchy. It’s an ideal setting to turn on for Department Supervisors who can be made responsible for the collection and approval of timesheets in their area. Since the report is hierarchical it doesn’t matter how many or few levels of approval there are, with this setting on, any timesheet data and only the timesheet data below the user will be visible. Now each department supervisor or timesheet clerk automatically has a Missing Timesheet report that applies to their department.

        The Missing Timesheet Report is a powerful tool and already goes a long way towards ensuring that any incomplete or unsubmitted timesheets are located but it is not the most popular tool among TimeControl Administrators. That privilege rests with the “Email Notification.”

        Email Notification takes the missing timesheet and automatically sends a pre-formatted email to everyone on the timesheet list. When starting this option from the Tools menu, it asks who you want to locate just like the Missing Timesheet report does. Then you’re given an opportunity to enter a message to users who may have a missing timesheet within their control. That means the employee themselves if the timesheet isn’t started or a supervisor if the timesheet has been submitted but is not yet own by the “Posting” process. The function uses the email address in each user’s record in the User table to determine where to send mail to. As good as that is, this function is made much more powerful by allowing TimeControl Administrators to automatically schedule emails to be sent out automatically! This means that an Administrator can schedule a gentle reminder to go out on a Friday (or near whenever the end of the working week is for you) saying “Don’t forget your timesheet”. They can then schedule an email to go out on Monday morning saying “Perhaps you forgot your timesheet?” and an email later in the morning telling the recipients that “No kidding, we’re waiting for your timesheet please!” As the day progresses, hopefully the list of recipients becomes shorter and the message more insistent.

        Both the Missing Timesheet report and Missing Timesheet Email Notification depend on the “Start” and “Finish” dates in the Employee record. You can think of those fields as “When we should start looking for missing timesheets” and “When we should stop looking for missing timesheets”.

        These are two of the most powerful tools but they’re augmented by a few other elements:
        First, the TimeControl dashboard can be set up to show users if they are missing any past timesheets as soon as they log into TimeControl.
        Next, the integration of TimeControl into processes like Project Management, Payroll and HR let the compliance of the timesheet in TimeControl adopt the standards of the compliance of those systems. For example, if the timesheet is key to having payroll done successfully, there’s an enormous incentive for employees to complete it. That helps on the project management side as the data is collected at one time. TimeControl’s ability to be a single source of timesheet data for many different systems is a big benefit in this regard.
        Finally, TimeControl’s functionality for “Alternative Users” who can replace a supervisor to approve timesheets when that supervisor is away for an extended period, “Change Ownership” which allows an Administrator to force an incomplete timesheet up the approval path when an employee has started it but is absent and the ability to automatically send emails for rejected timesheets that had an approval problem all lend themselves to helping make sure the timesheets are located each week and ultiamtely end up in a posted format.

        TimeControl’s ability to track down missing timesheets is one of the reasons it is so popular with such a wide range of sizes of organizations. This functionality scales to tens of users, hundreds of users or thousands of users as a natural part of the weekly timesheet process.

        Cascading Fields, timecontrol, timesheet, UDFs, User Defined Fields

        Cascading User Defined Fields

        Leave a comment

        Here’s the challenge. On a timesheet you have several user defined fields. Let’s take a simple example. We have consultants who do several things but we want them to identify what city they were in when they were doing the work this week. So, I could populate a drop-down list of 10,000 cities but scrolling through it to find Toronto would be quite tedious.

        TimeControl now accomplishes this with “Cascading User Defined Fields”.

        To activate this feature you’ll need to have access to the TimeControl Server and follow these steps:

        1. First, create your user defined Text Fields in the timesheet itself. You may want to hide them from others in the User Profiles until you’re ready to display them.
        2. Now, under the TC5 directory add a new folder called “CustomWork”
        3. In this folder make a file called “TimesheetUDF.ini”
          Here’s an example of what would be in the file. In this example, timesheet field 2 would depend on the value in timesheet field1, Timesheet field 3 would depend on the value in timesheet field2.

          [DEPENDENCY]
          TSD_FLD2=TSD_FLD1
          TSD_FLD3=TSD_FLD2

        4. In the fields themselves, we then use periods to distinguish the levels.

          So in TSD_FLD1 we might have:
          CANADA
          USA

          TSD_FLD2 might then have:
          CANADA.QC
          CANADA.ON
          CANADA.BC
          USA.NY
          USA.NV
          USA.FL

          TSD_FLD_3 might then include:

          CANADA.QC.MONTREAL
          CANADA.QC.QUEBEC
          CANADA.ON.TORONTO
          CANADA.ON.OTTAWA
          USA.NY.NEWYORK
          USA.NY.BUFFALO
          USA.NV.LASVEGAS
          UAS.FL.MIAMI

        5. You’ll need to restart your TimeControl Transaction Server for the new definitions to take effect.

        When entering a timesheet now, if you choose “Canada” in field 1, then only QC, ON and BC will appear in field 2. If you then choose “ON” in field 2, then TimeControl would only show “TORONTO” or “OTTAWA” in field 3.

        The effect is to reduce what is potentially tens of thousands of choices to a tiny handful and that can make the difference between a complicated timesheet entry and a very simple one

        The official blogsite of TimeControl