Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.
Excel help to maintain attendance
I have attached the excel i created
I wanted to add In and Out time also in this excel but have no idea how i can fix that into this current excel
When an employee comes in and goes out
Also i want help with the formulas to show the Present days and leave and pay also
Say salary is 12,000 Rs how will we calculate the final pay minus holdays
Attendance-Sheet.xlsx
I wanted to add In and Out time also in this excel but have no idea how i can fix that into this current excel
When an employee comes in and goes out
Also i want help with the formulas to show the Present days and leave and pay also
Say salary is 12,000 Rs how will we calculate the final pay minus holdays
Attendance-Sheet.xlsx
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You need to add columns for start time and end time.
You really need specialist software for calculating wages.
You really need specialist software for calculating wages.
1. Yes need to add a column but where can i add it as it needs to come within each day so not sure how
2. My goal is to check each month/no of leaves and calculate the salary
12,000 rs is monthly pay than daily its 400 rs - minus the days on leave
We cannot afford a softwares so can anyone help on this please
2. My goal is to check each month/no of leaves and calculate the salary
12,000 rs is monthly pay than daily its 400 rs - minus the days on leave
We cannot afford a softwares so can anyone help on this please
You need to add start time & end time for each day
Here's one that I adapted a few years ago
monthly-timesheet-ROY.xls
Here's one that I adapted a few years ago
monthly-timesheet-ROY.xls
Here is another way, keeping the presentation you have.
The file has 2 sheets.
Data:
The inputs including In and Out time.
It is a table, so just type the "Sl No" in the first row in column A after the table, then Excel adds a new row to the table.
Select (or type) P, H, O or A in column B, and if P then also In and Out time in columns C and D.
The other columns in the table (E:I) are formulas for date and day, and Name and Designation for information.
If it is an new "Sl No" the date is the DOJ date, else it is the next day for that "Sl No".
I have just filled in some random data.
Month:
Only one sheet to be used for all month.
Type the first date of the month you want to see in G2.
In F1 you can select to view Attendance or Hours.
Attendance show the types P, H, O or A, and Hours show hours for P and types for the other.
Columns A to F for employee information, the others are formulas.
Attendance-Sheet.xlsx
The file has 2 sheets.
Data:
The inputs including In and Out time.
It is a table, so just type the "Sl No" in the first row in column A after the table, then Excel adds a new row to the table.
Select (or type) P, H, O or A in column B, and if P then also In and Out time in columns C and D.
The other columns in the table (E:I) are formulas for date and day, and Name and Designation for information.
If it is an new "Sl No" the date is the DOJ date, else it is the next day for that "Sl No".
I have just filled in some random data.
Month:
Only one sheet to be used for all month.
Type the first date of the month you want to see in G2.
In F1 you can select to view Attendance or Hours.
Attendance show the types P, H, O or A, and Hours show hours for P and types for the other.
Columns A to F for employee information, the others are formulas.
Attendance-Sheet.xlsx
Thanks a lot Ejgil Hedegaard this looks promising
1. The No of holidays taken is LOP as we have no approved leaves
2. Salary for each will different so anyways to give different salaries..
3. H,P,O what are the differences? Can we have full forms of them
4. Always Sunday is a holiday can we mark them by default
5. Row 4 i added the first employee name in sheet "Month" and 62 times it shows in "Data sheet"
1. The No of holidays taken is LOP as we have no approved leaves
2. Salary for each will different so anyways to give different salaries..
3. H,P,O what are the differences? Can we have full forms of them
4. Always Sunday is a holiday can we mark them by default
5. Row 4 i added the first employee name in sheet "Month" and 62 times it shows in "Data sheet"
1. LOP are calculated as the number of days marked with A, just like the original sheet.
I don't know what A is.
How should LOP be calculated?
2. A column for the salary for each person could solve that.
You have to specify how to calculate the month pay, which days to use. P+H-LOP or?
3. H, P and O was used in the original sheet.
I guess H means Holiday as it was used for Sundays, and P means Present.
I don't know what O and A is.
"A" was used to calculate LOP, hence the selection possibility.
It could be the full name in the Data sheet, but then the columns in the Month sheet must be wider and you lose some of the view perspective.
A solution could be to only use the first character in the name on the Month sheet.
4. Sundays are automatically marked on the Month sheet by conditional formatting.
Automatically insert H (or Holiday) in column B on Data sheet when it is a Sunday is not possible by formulas.
You must also be able to type the value, and then the formula is lost.
It can be done by VBA programming, monitoring when the value in column A change.
It is not difficult to make.
5. The values in H and I are the values in column B and C on Month sheet.
Easier to see that the values are for the right person and day.
When you type "Sl No", then the day, employee name and designation are shown.
6. What are you planning to use the hours for.
I don't know what A is.
How should LOP be calculated?
2. A column for the salary for each person could solve that.
You have to specify how to calculate the month pay, which days to use. P+H-LOP or?
3. H, P and O was used in the original sheet.
I guess H means Holiday as it was used for Sundays, and P means Present.
I don't know what O and A is.
"A" was used to calculate LOP, hence the selection possibility.
It could be the full name in the Data sheet, but then the columns in the Month sheet must be wider and you lose some of the view perspective.
A solution could be to only use the first character in the name on the Month sheet.
4. Sundays are automatically marked on the Month sheet by conditional formatting.
Automatically insert H (or Holiday) in column B on Data sheet when it is a Sunday is not possible by formulas.
You must also be able to type the value, and then the formula is lost.
It can be done by VBA programming, monitoring when the value in column A change.
It is not difficult to make.
5. The values in H and I are the values in column B and C on Month sheet.
Easier to see that the values are for the right person and day.
When you type "Sl No", then the day, employee name and designation are shown.
6. What are you planning to use the hours for.
Sorry for the delay
1. LOP is Loss of Pay even a days leave is a days loss of pay
2. Say the employee's Salary is 12,000 Rs so its divided by 30 days by default and 400 rs is per day
We will have different ranges of salaries for each employee
3. Yes we can use as "L"
4. Please help with it
6. Hours would be entered by a security guard which he will update the no of minutes of hours an employee was out of office actually if for when he entered office and when he left its mainly used for
1. LOP is Loss of Pay even a days leave is a days loss of pay
2. Say the employee's Salary is 12,000 Rs so its divided by 30 days by default and 400 rs is per day
We will have different ranges of salaries for each employee
3. Yes we can use as "L"
4. Please help with it
6. Hours would be entered by a security guard which he will update the no of minutes of hours an employee was out of office actually if for when he entered office and when he left its mainly used for
Data sheet:
3 options for Type: Present, Holiday, Loss of Pay.
Macro added to automatically insert Holiday as Type if date is a Sunday.
Accept macros when opening workbook.
Month sheet:
Formulas in columns G to AK adjusted to fit changes on Data sheet.
Displays P (or hours if Hours selected in F1), H or L.
Columns AL to AR are calculated like this:
Present days is count of P, or cells with a positive value if hours selected in F1.
Holidays is count of H.
LOP is count of H and L.
Paid Days is equal to Present days.
Attended % is Present Days / Sum of Present Days and LOP.
Salary per day to be typed for each employee.
Total Pay is Paid Days * Salary per day.
Attendance-Sheet.xlsm
3 options for Type: Present, Holiday, Loss of Pay.
Macro added to automatically insert Holiday as Type if date is a Sunday.
Accept macros when opening workbook.
Month sheet:
Formulas in columns G to AK adjusted to fit changes on Data sheet.
Displays P (or hours if Hours selected in F1), H or L.
Columns AL to AR are calculated like this:
Present days is count of P, or cells with a positive value if hours selected in F1.
Holidays is count of H.
LOP is count of H and L.
Paid Days is equal to Present days.
Attended % is Present Days / Sum of Present Days and LOP.
Salary per day to be typed for each employee.
Total Pay is Paid Days * Salary per day.
Attendance-Sheet.xlsm
Premium Content
You need an Expert Office subscription to comment.Start Free Trial