mtthompsons

asked on

# 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

ASKER

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

ASKER

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.

ASKER

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

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

You really need specialist software for calculating wages.