Link to home
Start Free TrialLog in
Avatar of 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
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

You need to add columns for start time and end time.

You really need specialist software for calculating wages.
Avatar of mtthompsons


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
You need to add start time & end time for each day

Here's one that I adapted a few years ago
Here is another way, keeping the presentation you have.

The file has 2 sheets.

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.

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.
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. 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.
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
Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial