Page tree
Skip to end of metadata
Go to start of metadata

Title: Corrections to LOCKED/EXTRACTED time via Time Import

Brief description:

Occasionally it may be necessary to correct data in your system due to user error. For example, if pay codes, project types, labor cost elements are recognized as incorrect, you will need to correct those entries. Or, if you need to move hours from one Project/Task to another Project/Task. While corrections can be made in the user interface, it may be more efficient when numerous corrections are required to make the corrections via a Time import. This method can be very handy when making Adjustments in bulk.

This entry describes a process for exporting the incorrect time and importing it via two correcting files. One file is a “0” hour file that will be imported with the Replace function to negate the incorrect records. The second file imports the correct values.

What’s covered in this document:

Generate the data export file

  1. Run a standard Time Export using Time Import Format from Admin > Export. If desired, a copy of the Time export template can be made to add conditions or logic (say to filter for specific Task Names).



     

  2. In the Run Export criteria, specify the settings for Date Range, Timesheet Status, and Adjustments.
    1. Adjustments should typically be set to Include Adjustments Using Worked Date, Include previously extracted and Suppress intermediate adjustments but, that may vary based on your system data.
  3. Download the file and save it as the "Original" file.

Review the file for negative hours

Review the file and verify there are no negative hours as these will need to be removed from the original file. Negative hours are adjustments and cannot be imported. The negative may represent a negative adjustment only (hours moved to another project/task), or the hours may have two rows with positive and negative hours (in and out) but the labor category, location or other value changed. These rows will be deleted from the file. Ensure that the only rows on the file are values that need to be corrected. Save the file.

Create a second file to delete the incorrect values

  1. Create a second file by saving a copy of the Original, called something like "Negating" file.
  2. Change the Hours column to "0" for all records that need to be corrected.
  3. Copy the dates in column B (“work_date”) and paste into column T (“post_date”). If you do not supply a Post Date, the date will default to the date of import which may cause future reporting problems.

You now have two files, the "Original" file and the "Negating" file.


This "Negating" file shows that the first two rows have been edited to zero, which will, when imported, replace the hours for Task 1 on those dates with a zero. The remaining three rows would also be edited prior to import.

Create third file to import the correct values

  1. Create a third file by saving a copy of the Original, called something like "Move Hours" file.
  2. Update the file to apply the correcting values (if you are moving time to a new project/task, then you would correct the old project/task to reflect the new one in this file).
  3. Copy the dates in column B (“work_date”) and paste into column T (“post_date”). If you do not supply a Post Date, the date will default to the date of import which may cause future reporting problems.
  4. Review all fields in the file for accuracy.


This "Move Hours" file shows that the first two rows have been edited to place hours onto Task 2; the remaining 3 rows would also be changed. 


You now have three files, the "Original" file, the "Negating" file, and the "Move Hours" file. 

Checklist prior to importing time

  • All users must have a begin date equal or earlier than the first date in your import file.
  • All users must be active.
  • Turn off emails.
  • Turn off reviews.
  • Consider Admin Access ONLY.
  • If you have any inbound integration for assignments or labor categories, turn off the import until time you are importing is LOCKED/EXTRACTED.
  • If you have an inbound integration with schedule imports, turn off the importer or do your import at a time well outside of the scheduled imports.
  • Double check for zeroes, negatives, and duplicates in the "Move Hours" file - these should have been removed.
  • All users must have the role Timesheet User in the Person profile.
  • Remove any data from the file that does not need to be changed (i.e. hours charged correctly).
  • Via an export, Mark adjustments as Extracted.
  • Turn off dilution only if your file has effective cost rates that you want to apply (this is turned off in Admin > Properties).

Import the "Negating" and "Move Hours" files

  • Have two tabs open with the same Project Time Details Report to track imports and verify the adjustments. You will refresh one after the correcting import to compare to the one that was run prior to the correcting imports. Remember not to refresh the original report run prior to the imports - as a safeguard you may want to download the report as a pdf prior to doing any imports. 
  • Import "Negating" file.
    • Choose the Cell Update Method of Replace (this will wipe out the incorrect entry and replace it with a zero).
    • Refresh the Time details Report and verify that the data was uploaded as you intended. If you have zeroed out all the values in your original report then you should get "No Data" returned, or if including Adjustments you should see 0 hours.
    • The Allow Adjustments box will need to be checked.
  • Import "Move Hours" file.
    • Chose the Cell Update Method of Append (this will apply the correct entry).
    • Resulting Status should be set to LOCKED unless you want the data to go through the approval process again.
    • The Allow Adjustments box will need to be checked.
    • Refresh the Time details Report and verify that the data was uploaded as you intended.
  • Other time import settings:
    • For Locked/Extracted timesheets, the "First Purge" and "Locked/Extracted" settings are ignored and may be disregarded.
  • If any ERRORS occur on import, DOWNLOAD AND SAVE THE ERRORS FILE. Correct issues and reimport the file.


Navigate: Admin > Import > Type of Import: Time. 

Example

In the example below, we are moving time from one project to another. 

Time prior to any corrections is all on PROJECT: SPACE-STATION when timesheet is SUBMITTED, APPROVED, and LOCKED. 
The resulting Project Time Details report for the period is shown below.


After being LOCKED, the timesheet is then edited to moved the hours to PROJECT: BIG-DATA. 
The Project Time Details report is run again and the adjustment displays as follows.



Additional Information

Help Docs - Time Import









  • No labels