Can you please assist me with adding two new fields from timesheet into my Macro

[Bec Connolly]

Hello,
Can you please assist me with adding two additional two fields into my macro.
The macro is to currently converting a timesheet into a csv import file.
The new columns which need to be included in the macro are Column A Row 7 (Costing), and Column B Row 3 (Rateset).
An example of the timesheet import file is on the second tax (Example Of Import File).
Many thanks for your assistance with this.
I look forward to hearing from you soon.
Kindest Regards,
Bec

[x65140]

You mean something like this?

[Bec Connolly]

Yes that is perfect. Thank you so much for your fantastic assistance.

[Bec Connolly]

Opps one more thing. I have just added a formula the emp timesheet tab for Saturday,Sunday and Public holiday as it was not working the hours out correctly. I then ran the macro and it ended up with a line for every component on every day
It is possible to change this so it only displays on the import sheet any date with a component on it with a value.
I have attached the spreadsheet again.
Thanks a mil for all your help.
I look forward to hearing from you soon.
Bec

[x65140]

See if this is what you are looking for....

[Bec Connolly]

Hello and many thanks for your reply.
I opened the file up and ran the macro, then i went into the import template for the employee to view the file to be imported and noticed it had all the components listed next to the day with or without a value.
Is it possible for the template only to pick up any days with a value against it's component.
I look forward to hearing from you soon.
Many thanks again for your fantastic assistance

[x65140]

Hey Bec -
made another change....hope it helps.

[Bec Connolly]

Hello Again,

Many thanks for your fantastic assistance. The file is perfect.

Your a super star for helping me.

Have a great day, and thankyou so much again for your remarkable customer service.

Kindest Regards,
Bec

[Bec Connolly]

Hello Again,

I promise this is the last time i will ask for assistance on the import file.
I just ran the macro and did some testing in our system.

In doing testing i came across a few little things i need some help with.

1. I updated the employee timesheet with cost accounts for the whole month and i noticed they only came through up until the 24/02/2015.

2. The formula for Saturday / Sunday and the Public holiday did not come in properly, are you able to assist with the formula for those columns M, N and O. I copied the formula from another column however as you will see on eg: 07/02/2015 and 8/02/2015, the hours ended up in the Normal time column and not the correct weekend columns (Saturday and Sunday

3. One last thing i entered a employee name with over 25 characters and it came up with a run time error of 1004. Are we able to reset the field size in the macro?

Are you able to help me one last time.

Thankyou so very much, you assistance is greatly appreciated.

Kindest Regards,
Bec

[x65140]

Bec....
1 - date range working
2 - changed formulas, they 'seem' to be working
3 - tabs only allow 31 characters. You have the person's name plus 13 extra characters for the date.
(i.e. Margaret Smith1234 works, but Margaret Smith12345 will not)
* attached BEC3.xlsm file

[skywriter]

Hi Bec,

I saw this thread and thought your name sounded familiar. I'm the one who originally helped you with this spreadsheet. I found the original thread and I see where you came back with another request a few days after your last post. I apologize for not answering that request, but I usually unsubscribe from a thread a few days after I think it's solved, which means I don't get notified of any further posts on that thread.
In the future if you post a request a few days after the last post and don't get a response you can always PM the person who was helping you in case they unsubscribed from the thread.
Anyway having said that I see you've been helped with your current issue. Regarding the issue with the tab name as x61540 has pointed out there's a 31 character limit on the name. I rewrote my original code to take this into account. What it does is if the name will be over 31 characters it makes the employee's first name an initial only, in the sheet name. See the example in the attached sheet. I changed her name to Smithsonian and if you run it you will see it changes the sheet name. Change her name back to Smith and run it again and you will see the difference.

If it's still too long you will get a message box informing you that it's still going to be too long. There's other things I could do, for instance if you think the person's last name will be the real issue we could cut that down to just an initial or in the case of a situation where the name is too long no matter what we do we could use the employee number, or cut off the date etc.
There are several possibilities.
Good Luck!!!

[Bec Connolly]

Hey Again,
Are you able to help me add a few more things into the Import File and Remove the ratesets from the import file.
What i need for the system to accept the file is:
Column A (Emp Code) Add Emp Id to all lines
Column B (Periods) Add 1 to all lines
Column C (TrxType) Add N to all lines
Column E (Rateset) Please remove the Ratesets from all lines.
Attached is the file i ran and have highlighted the fields in yellow on the import file which need changing.
Thanks a mil for all your help.
Kindest Regards,
Bec

[x65140]

See if this works...

[Bec Connolly]

That is perfect. The import worked 100% with no errors. Well done and thankyou so much for your help.
Have a great day

[Bec Connolly]

Hello Again,
I hope you had a nice Easter.
Are you able to help me again write a formula into my spreadsheet.
I needed to add to the spreadsheet on the employee time sheet a break start and break finish time. I then put a formula into column H under meal breaks to subtract the meal finish time (column F) from the meal start time (column E). The formula worked for most cells except for a few which i have highlighted in green. (Are you able to come have a look and see what excel is doing as i am completely stumped.
Also can you please help me to write in a formula for the award into the Columns K (MA7), Column L (N) and Column M (MMID7)> The formula which needs to be added to the already existing formula is to take the meal break out of the component after the employee has completed 3.5 hours into their shift. An example of this is on the employee timesheet for the work completed on 02/02/2015, the employee start time is 18.00 and the finish time is 24.00, the break is 30 minutes, so the total hours should be 6.00 hours. What the award is doing on the timesheet is taking the break from the first component N 0.30 and putting the additional hours 5 onto MA7. (What the award should do is taking the break from the MA7 as that would be 3.5 hours into the shift. The end result would be 1 @ Normal and 4.5 @MA7.
Are you able to help me ?????
Kindest Regards,
Bec

[skywriter]

The problem in you break start and finish formulas is your number formatting. The number format you are using is not a time format. It's just a format that allows you to type 1130 instead of a time format where you have to type 11:30 which is how you would enter a time. So if you look in cell F7 you think you are entering 20:30 and in E7 you think you are entering 20:00, but you really entered 2030 and 2000 and when you subtract the two you come up with 30. You have the same formatting in H7 so you think you've received a result of 30 minutes(0:30), but this is not a time. Now look in cells F19 and E19 where you expect to the result of 30 minutes and your result is 0:70, it's because you are doing the math on these numbers 1200-1130, which is in fact 70 because you didn't enter times you entered numbers into a cell that has formatting that inserts a colon into the number. I notice in your K column you have some formulas where you are constantly using the Text function and I'm not an expert on dates and times but I wonder if it wouldn't be better to just format everything as times and enter it as times and do date time math instead of all that text stuff.