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
mtthompsonsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
You need to add columns for start time and end time.

You really need specialist software for calculating wages.
0
mtthompsonsAuthor Commented:
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
0
Roy CoxGroup Finance ManagerCommented:
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
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Ejgil HedegaardCommented:
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
0
mtthompsonsAuthor Commented:
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"
0
Ejgil HedegaardCommented:
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.
0
mtthompsonsAuthor Commented:
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
0
Ejgil HedegaardCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.