Employee Work hour integration with QuickBooks Desktop
QuickBooks desktop allows to import timesheet data as IIF (Intuit Interchange Format) files. IIF format is a text-based tab-delimited format very similar to .tsv file format. This guide describes how you can migrate NCheck Bio Attendance total work hour report (Daily) data in IIF format. To create such IIF file, user need to perform these main steps.
- Import work hour data (Daily) from NCheck bio attendance as a CSV file.
- Manipulate work hour report to format data and remove unnecessary data.
- Download sample IIF file.
- Update correct header information using “Timer list” IIF file.
- Merge manipulated report data.
- Import to QuickBooks.
1. Import work hour data (Daily) from NCheck bio attendance
- Login to NCheck Bio Attendance and go to reports tab.
- Select “Total work hours” report and set “Sum by” as “Daily”.
- “Show time” should be in “Hours and minutes”.
- Select the required date range and refresh the report.
- Select “Export” button and select “CSV” to import report in CSV format.
2. Manipulate work hour report to format data and remove unnecessary data
Open the csv file using a spreadsheet software, such as Microsoft Excel. Do the following operations on that file.
- Concatenate “First name” and “Last name” columns and create new column “EMP”. Refer this for more help.
- Rename the “Work hour” column to “DURATION”.
- Delete “From” date column and set “To” column date format to DD/MM/YY format. Also, rename the column header to “DATE”
- Delete all columns other than “DATE”, “EMP” and “DURATION”.
3. Download sample IIF file.
You can download the sample “. IIF” file here. You can re-name the file as you wish. Open it using a spreadsheet software like Microsoft Excel.
4. Update correct header information using “Timer list” IIF file
“Timer List” IIF file can be exported from QuickBooks using File>Utilities>Export>Timer Lists.
Exported Timer List IIF file header will have the Company details.
Copy the first 4 lines highlighted in yellow color. Replace the 1st 4 rows of the sample file with this content. Now sample file will look like this.
5. Merge manipulated report data.
Follow these steps to merge work hour data we just modified to this IIF file.
- First column value should be “TIMEACT ”
- For “DATE ” column, merge the contents from the work hour report you just modified.
- “JOB ” column – The name of the customer (or job). If you’re entering the name of a job, enter the customer’s name followed by a colon followed by the name of the job. Both the customer and the job names must also be on your Customers & Jobs list (CUST ).
- “EMP ” column – merge the contents from the work hour report you just modified.
- “ITEM ” column – The name of the service item. The service item must also be on your Item list (INVITEM ).
- “IPTEM ” column – Payroll Item for Paychecks.
- “DURATION ” column – merge the contents from work hour report you just modified.
- “PROJ ” column – The QuickBooks class assigned to the activity (classes give you a way to group activities in meaningful ways in time reports). The Class must also be on your Class list (CLASS ).
- “NOTE ” column – Description/Note for each time entry. You may enter up to 1000 characters.
- “BILLINGSTATUS ” column – Indicates the billing status. Enter one of these values: 0 – not billable, 1 – Not billed, 2 – Billed
The result file format looks like below image. Once it is done then the Report is ready to be imported.
6. Import to QuickBooks
Now your worksheet IIF is ready to import in to QuickBooks. Go to File>Utilities>Import>Timer Activities to import the worksheet IIF file. Once data is imported, a Timer Import Data report will be shown the data which was extracted.
Done! Now tour timer data will be imported to QuickBooks.