Converting a Timesheet To a csv import file

[Bec Connolly]

Hello,

I am wishing to convert a timesheet into a csv import file. I am not sure if it is best to create a macro or build a formula on Sheet 2 to pick up the information to be imported into the payroll.
The timesheet attached is for a monthly employee. The employee will put into the Excel timesheet the date, start time, break time and end time per day.
I have then created a example timesheet of the columns to work out at what pay component the hours are to be paid at.
Could you please help to advise how i can get the information from the timesheet in Sheet 1 to sheet 2 so the import layout is correct.
Also i am stuck with the formula for Saturday, Sunday and Public Holiday. Could you please help.....
Thanks a mil
Bec

[skywriter]

If you are not opposed to a macro I can get the data into the import file, but I would need to know with the N, M7, A7 do you only want them if they have actual hours and also what order should they be in. I'm also assuming this would go into a template sheet or you want everything including the labels built from scratch. You have some items at the top with no data what are these, will they ever have data. I need to no all the possibilities. Example RecomDte there's nothing below this, Reason, Tax PCV Costing etc. Best thing to do would be to show me a filled in timesheet and a 100% completed import sheet built from the actual numbers on the timesheet.

[Bec Connolly]

Thankyou so much for replying back to my query. The Import File (Sheet 2) is the correct layout required to import into the payroll system. The fields that require information are:Payrun group, Employee Code, Period End Date, DteFrom (which is the date of the work completed), PCC (Pay Component Code) eg: N, A7, M7 etc and the PCU (Pay Component Units) only if they have a unit value against them.
The other fields do not require information in them at this stage. The Order of the Pay Components if possible can it be M7, N, A7, SAT, SUND, P
The fields highlighted in yellow are the required fields. I only put a couple of days work in the spreadsheet to give you an example of what the timesheet will look like. What the end result of the timesheet will be is a timesheet from the employee with all their days worked in the month.
I am not very advanced with Excel, the end result which would be nice is to have Sheet 2 convert the employees timesheet from Sheet 1 into a file which can be saved as a csv file and imported direct into the payroll system.

[skywriter]

Okay Bec here's the time sheet.

There are some things you need to know.

1) There's a sheet named "ImportTemplate" that sheet is being used by the code and the name must not be changed and it must remain unchanged. You can copy it and use a copy but don't change the name or delete this sheet.

2) To run the macro you must have the time sheet that you want changed into an import sheet selected. Just select the tab if you are on another sheet, it doesn't matter what cell you are in it just matters that the sheet is active when you run the macro.

3) In order to convert the hours that appear like this 6:30 into integers the code must multiply by 24, but since your SAT and SUND hours are integers I made an allowance in the code for this. Only thing is you don't have any hours for P, so I assumed they were like the SAT and SUND hours, if this is not the case let me know.

4) When the code makes a new sheet it names it with the employee name and the period end date. If you want to run this code more than once just to see it in action, then you must delete the import sheet that was previously created or you will get an error when the code tries to name a sheet with a name that already exists on another sheet.

So go to macros wherever that is in your version of Excel and run the macro, it's the only one there.

Good Luck!!!

[Bec Connolly]

Thankyou so much for your kind assistance and support in helping me with the import file.
Your services are really fantastic and remarkable.
Have a great day.
Kindest Regards,
Bec

[Bec Connolly]

Hello Again and many thanks for your assistance with the macro last week.
Two more columns with information need to be added into the Import file for the Costing and the Rateset.
Are you able to assist with making changes to the current macro so when the macro is run it includes the additional columns.
Many many thanks for your assistance.
Kindest Regards,
Bec