Question
How to convert Actatek exported employee time entries to Pacific Timesheet timesheets.csv import format?
Answer
Background
There might be times when you need to export time entries from your Actatek time clocks and import them into Pacific Timesheet. This requires steps to convert the Actatek time entry exported data into the Pacific Timesheet timesheets.csv import format. Here are the steps.
Step 1 | |||||||||||
1. Window into the Actatek time clock logged in as an Administrator. 2. Then navigate to User Administration > Daily Report. 3. Select a desired date range, set the Export preference to .csv, and 4. Then run the Daily Report. The .csv Daily Report data will look like this: |
|||||||||||
User ID | Name | Date | Weekday | First In | Last Out | ||||||
860001 | John Smith | 3/7/2023 | Tuesday | 1:25:34 | 12:00:12 | ||||||
860001 | John Smith | 3/8/2023 | Wednesday | 1:14:52 | 11:54:02 | ||||||
Step 2 | |||||||||||
Delete columns B & D | |||||||||||
Then change headers as follows: First In => Start Last Out => Finish |
|||||||||||
Now the file should look like this: | |||||||||||
Employee.ID | Date | Start | Finish | ||||||||
860001 | 3/7/2023 | 1:25:34 AM | 12:00:12 PM | ||||||||
860001 | 3/8/2023 | 1:14:52 AM | 11:54:02 AM | ||||||||
Step 3 | Start formula | ||||||||||
Concatenate Date & Start, Date & Finish as text values using this formula: | =TEXT(B2,"m/d/yyyy")&" "&TEXT(C2,"hh:mm AM/PM") | ||||||||||
|
Finish formula | ||||||||||
=TEXT(B2,"m/d/yyyy")&" "&TEXT(D2,"hh:mm AM/PM") | |||||||||||
|
|||||||||||
Employee.ID | Date | Start | Finish | Start | Finish | ||||||
860001 | 3/7/2023 | 1:25:34 AM | 12:00:12 PM | 3/7/2023 01:25 AM | 3/7/2023 12:00 PM | ||||||
860001 | 3/8/2023 | 1:14:52 AM | 11:54:02 AM | 3/8/2023 01:14 AM | 3/8/2023 11:54 AM | ||||||
Step 4 | |||||||||||
1. Copy Start and Finish formula cells as text values to Start and Finish columns | |||||||||||
2. Copy cells | |||||||||||
3. Paste Special as Values (123) to the target cells | |||||||||||
4. Delete Date column | |||||||||||
5. Save file | |||||||||||
Step 5 | |||||||||||
The resulting data should look like this: | |||||||||||
Employee.ID | Start | Finish | |||||||||
860001 | 3/7/2023 01:25 AM | 3/7/2023 12:00 PM | |||||||||
860001 | 3/8/2023 01:14 AM | 3/8/2023 11:54 AM |
For additional instructions on how to concatenate date and time values, view this YouTube video:
How to combine date and time columns in Excel
Comments
0 comments
Please sign in to leave a comment.