Hi,
Im new here and im just an average excel user. Im trying to create a simple weekly timesheet with payroll. I want the time inputs to reset when changing the date.
How can i do this? Attached is my excel file.
Hi,
Im new here and im just an average excel user. Im trying to create a simple weekly timesheet with payroll. I want the time inputs to reset when changing the date.
How can i do this? Attached is my excel file.
and data should change based on the employee name.
copy this macro to work sheets "Weekly Time Sheet". Hope is what you want!Please Login or Register to view this content.
"and data should change based on the employee name." can explain more?
-If the problem is solved, please mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved".
-Always upload a workbook before start your question
To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.
+++ If my answer(s) helped you, please add me reputation by click on * +++
Thank you.
Sorry but i don't know how to use macro, I'm still searching on how to use it.
I mean time in and time out data should based from the name of the employee and date.
Thank you.
Sorry but i don't know how to use macro, I'm still searching on how to use it.
I mean time in and time out data should base from the name of the employee and date.
I tried saving it on "weekly time sheet" but nothing happens. When i scroll the date, time in and out are still there.
Please find attached file, no sure is what you want or not. If not please do some samples for with more explanation.
Time in and Time out goes back to zero now after saving, but why cant I see the time in and out inputs on that particular date when i check it
again? I'll make an example. Thanks.
Pls see attachment. Basically what i want is, Data inputs should change depending on the date and employee..
If employee Batman has a total 45 hours on 1st week, 50 hours second week and 0 on 3rd week.
Superman has 0 hour 1st week, 20 hours second week and 0 hour 3rd week.
If i choose the employee Superman and choose 2nd week, the data on total hours should display "0" or blank
and if I change the employee to batman and 1st week, the total hours should display its corresponding data which is 45 hours.
I think you already know what i mean.
TIMESHEET TEST(v2).xlsm
Like this?
I tried analyzing the macro you use so that i can add more data to save but this is beyond my comprehension. hahaha...
I'm just a basic excel user.
I want to add an automatic payslip stab generator based on their data, i already made a sample.
Thank you so much!!
I don't get what your mean.
1. So you want retrieve data from which worksheets?
2. There are 8 block need to fill in sheet "Salary Receipt", each block get data from?
3. What is the work flow you expect?(change date in "Salary Receipt" will get all data?Or change name?or???)
1. I want to retrieve data from "timesheet" worksheet. Basically all data should be input here. time in time out, deductions, rate then will be save on "save" worksheet just like what you did.
2. There are 8 blocks=1 letter size bondpaper. So # of blocks automatically generates depending on how many employees has an attendance on that payroll week.
e.g; employee list: a b c d e f g. Payroll Starts from day 7-13. Only employee b c d e f has an attendance that week or at least one attendance in that particular paydate.
So salarayreceipt blocks=# of employees with attendance that week = a b c d e f (6 blocks) if more than 8 blocks it goes to another page (second letter size paper) for printing purposes.
3. workflow in salary receipt; CHANGE date then get all data e.g; name of employee, designation, gross salary, deductions, net salary etc.
I'm sorry if my explanation is kinda confusing.
Sorry double post.
PAYROLL TEST(v1).xlsm
I only make 4 block.
1. Change the MASTER DATE, and it will list down who working on that week.
2. Assumed there are only max 8 ppl work in a week.
3. Each block get name from cell O6-O12
4. Then each block have its formula to retrieve data from sheet(SaveData) by Employee Name and Date.
Exactly what I wanted.... Thank you so much!!!
I'll start from this file.. I'm still trying to figure out how to add blocks on salary receipt..
I tried copying cells in "SaveData" and adding another name. From timesheet it saves fine to "SaveData"
but I still can't figure out how to reflect it to salaryreceipt blocks. I tried copying 1 block and changing the values
but it still won't appear. Only 4 blocks are generated with names.
PAYROLL TEST(v2).xlsm
I redo 8 block for you.
How to use SALARY RECEIPT
1. Change the MASTER DATE.
2. It will list down who working in that week from cell O6-O12, if blank mean nobody work on that week.
3. Each names will reflect to each block
Name1 Name2
Name3 Name4
Name5 Name6
Name7 Name8
4. Then based on Name and Date in the block, it will lookup its "Daily Rate", "Location Assigned", "TOTAL DAYS PRESENT", "CASH ADVANCE", "HARDWARE ITEMS", and "OTHER DEDUCTIONS" from sheets "SaveData"/
Remark:
1. The "SaveData" sheets required you input all your employee name, please follow the example format "CARLOS PAGUNSAN".
2. The date you have to include yourself too. In the sheets is start from 1st Sep.
Edit - Reattached correct file!
Last edited by wenqq3; 09-09-2014 at 10:50 PM.
I think you attached the wrong file?
Number of employees in a week that need to be paid are more than 8, about 40-50 or more. Can i make more blocks based on your 8 blocks?
Because if i tried to copy the blocks, name wont appear or do i have to edit the macro to add more blocks?
Last edited by valkrey2000; 09-09-2014 at 10:49 PM.
Reattached the file in #18.
1. Yes, you can make more block if you want. New block, required you to edit the refer cell in formula.
2. Or you can delete the 1st 8 name, can copy and paste another 8 name into it (by macro will be faster).
BTW, if you dont wish to check the data before print. Macro can help you print out all salary receipt.
Im having problem with the "Savedata". I followed the format from CARLOS PAGUNSAN, COPIED all the cell format and past in the right side at the last part (5th employee). But it won't reflect to "SALARY RECEIPT". only 1-4th employee reflects to salary receipt. a bit confused. hehe..
And also Deductions wont change based on the employee name and date in the "TIMESHEET"
1. This 1 is not save in Sheets "SaveData".And also Deductions wont change based on the employee name and date in the "TIMESHEET"
2. You need it be clear?
Can you upload the file?Please Login or Register to view this content.
1. Yeah, If possible i also want that to be saved.
2. Pls check my attachment.
Btw, just tell me if im asking too much already.. hehe thank you..
PAYROLL TEST(v2).xlsm
The salary receipt sheets. You said there is more than 8 person per week.
How you going to display?
1. Check and print out 8 block each time?
2. No need check and print out all 1 time( 8 block in 1 page)
3. Create 100 block and check before print.
-------
1. Where and how you and save the total pay?
2. Total pay per week?
PAYROLLOLOLLLL (v2).xlsm
Yes, AGAIN! Please find correct file.
PAYROLLOLOLLLL (v3).xlsm
1. I prefer this one but i also want to see it before printing. something like a print preview.
>Then you need increase the block by yourself.
>copy left and right block and paste below
>then change the employee's formula.
>example: previous left block employee is O19, then new left block must be O21.
>example: previous right block employee is O20, then new right block must be O22.
2. in a different sheet but within the same worksheet same as the "savedata", maybe salary expense record. this saves the total expense for the salary with in that payday
> i think you no need save, when you want check the total salary, just change the date in sheet(Salary Receipt), and then total will show you.
> TOTAL SALARY RELEASED need change the formula when you increase the block.
I think this is it.. and its almost perfect from what i want it to be or maybe it is alreay perfect!! I'm still checking on it.
Thank You very much!!!
Okay, let me know if you found any bug/issue.
wenqq3,
There's a bug in the salary receipt when adding blocks but i managed to edit the macro.
Can I ask for 1 last favor? I can't figure out how to edit the macro regarding the total days.
It automatically rounds up to 1 day even if the employee has only half day or less.
pls see attached file.
Thank you!
Please find attached file.
I really appreciate your help man... you're the only one helping me in this forum.
Welcome.Everyone here are helpful.
Since i start 1st, sure i need follow up to solve your problem.
Thank you again. you solved my problem. (y)
I didn't notice the attachment earlier..
I think this is it already.. I'll just enhance it a bit.
THANK YOU SO MUCH wenqq3!!!!
I didn't notice the attachment earlier..
I think this is it already.. I'll just enhance it a bit.
THANK YOU SO MUCH wenqq3!!!!
I want to edit the macro. Save the "location assigned". because the location assigned in the salary receipt wont update
based on the timesheet and it doesnt have a cell in the savedata.
Can you teach me how?
Sometime i can't get email. PM me if i'm not reply.
v5
1. Add location assign to "SaveData"
2. SalaryReceipt will refer to "SaveData"
3. Change name/date in "TimeSheet" will try retrieve back location too, if can't find will put BLANK
BTW, i don't know how to teach. Perhaps you can google and learn. I do this all the time.
OK... I'll try this payroll on payday tomorrow.
Thank you so much!!!!
I'm Having problem adding more name in "savedata" based on your attachment. If i add more names, it wont reflect to salary receipt.
This is my updated file.. I cant add names in save data on your last attachment. I tried copying your macro in this file but it wont work.
I want to save location assigned in this file.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks