Free Microsoft Excel 2013 Quick Reference

Record employees sick days holidays Results

Hi Group

Hi have a calender with days in the columns and months in the rows. However I have 3 rows for each month. I'm using text and values combinations in the cells to record data

Example First Sheet

Feb Rows, 11,12&13
Columns C,G Dates 1st to 5th

Therefore 1st of Feb has 3 cells to enter data, ie Basic Hours, Sickness, Holiday

Currently I'm defining a name range for each month, ie DFeb. Currently I'm recording each of the rows into columns in another sheet and the date in the row, by using a Hlookup on the date and a vlookup on the month

ie Second Sheet

Coulmn A ColumnB Column C Column D
row 1, 1-feb-xx, Row 1 for Feb, Row 2 for Feb, Row 3 for Feb
row 2, 2-feb-xx

Is their a better way to do this and to only record when data when something is entered into the cell rather than all the data? Also this is the only way that I know how to account for something if something changes to a record

Any help or suggestions would be appreciated.

Just wondering if anyone can help? I have created a spreadsheet for recording employee attendance. This captures the number of holidays, sickness days, unauthorised absences, etc. What I’m stuck on though, is counting the number of occurrences of sickness. I can capture how many days are taken in a year, but I need to know the number of times they are off (for example, if they are off once in January for 5 days, once in March for 2 days, and once in September for 1 day, this would be a total of 8 days). This is no problem, but what I need is to be able capture this as being 3 occurrences. Sick days are represented by a letter ‘S’ on the status list, with a normal working day being a zero (working hours are recorded on a separate list). So ideally, I need to know how many groups of single or multiple ‘S’s there are, separated by a zero or other letter.
Any help would be greatly appreciated. Thanks.

Each workbook sheet has payroll records per individual for the entire
year (regular hours, overtime, holiday etc).

I want a summary sheet to display the relevant data (ie: sums of regular
hours, sick days etc) of the payperiod entered (1 of 26) for each

So the clerk (me) enters a payroll period, and sees that data for each

Seems simply enough, and there is probably more than one way to do it,
but the help files have only frustrated me so far.


Worksheet which summaries staff used leave and calculates reaming leave allocation, all values summarised on new sheet [sheet name “SummaryLeaveSheet” ]

Hi all,

I would like help creating a method, formula or VB code which can look through 12 worksheets [named “LARSheet1” to “LARSheet2” ]; find an employee’s pay number on a row (say B25) and return a count of each attendance (“a”); lateness (“L”); holiday (“am or pm”) or Sickness (“S”) record on that row. The row may change on different worksheets but the search method can always use (B25) as a starting point.

With the found values, I would like a total count to be returned on a summary worksheet [“SummaryLeaveSheet”]

I would also like to list the dates each leave was recorded on the various worksheets [i.e “LARSheet1” to “LARSheet2” ] in a table.

Ideally one summery sheet could be used for all employees, the information would update and change based upon a pay number located in a cell linked to a ‘data validation’ drop down list.

Tall order I know….. Any help out there???

Many thanks, moshjosh

Example of layout of [“LARSheet1”]


===== ======== ====== ======== ====== ============= ========== ============ ============ ===== ========= =======
This is a multi-part project which is collectively known as “Leave, Attendance Management” system using Excel”. . Other parts are found on other treads. I hope to link the thread solutions together so others who wish to do a similar task can hopefully learn from help I receive (and my mistakes).

The other parts of the system are:

‘Leave attendance record’ worksheets (i.e months April 2012 through to March 2013). [I will refer to these sheets as "LARSheet1" to "LARSheet12"].

‘Data validation’ worksheet which holds list and vlook-up info for formulas. [I will refer to these sheet “FormulaListSheet” ].

‘Database’ worksheet acting as a of staff details database. [ “StaffdBaseSheet”].

‘Summary reaming leave’ page (this thread) which shows a summary of staff used leave and calculates reaming leave allocation. [ “SummaryLeaveSheet” ]

This is my first time using this website, so please forgive my inexperience with Excel and any daft questions I have.

I am trying to create a spreadsheet to manage employee time worked. It will record hours worked, plus any days (or portions of days) taken for different types of leave.

My problem is that I don't know what type of formula in C62 to use to say "if there's an 'S' in cell C8, then add value in D8; and If there's an 'S' in C9, then add value in D9, and so on, so that I eventually get a total of all sick days that employee 1 has taken in the month, i.e. 2 .

Because there will always be the need to record, say, a half day worked and a half day's holiday, I have left 2 cells available for each date, one to record W (hours worked) and one for all other variables.

If anyone knows what type of formala I should be using to work this out, I would extremely grateful. I'm a bit of a novice when it comes to any formulae past simple SUM etc!

I will be using a tab for each month, and link the totals into future months. Think this should be easy enough to work out.


I have an excel spreadsheet to record employee holiday and sickness figures.

It is set on as a grid e.g. column A stores all the dates and then employee names are used as column header.

One of the triggers I need to use is where, an employee has been absent 28 consecutive days. When an employee is absent I simply enter 'ABSENT' against there name.

Countif will count the number of time absent appears in the column however I need it to only recognise it if it is only 28 days in a row.

Any help would be much appreciated!


hai everyone.
Happy Friendship day to all of u.

I wish someone could help me on:
Column A has names of employees. The number of employees changes from sheet to sheet. Iam doing a calender program which record the number of days the employee was on leave/sick/holiday etc. B1 to X1 will contain the day-numbers of the month. Why X ? Feb has only 28 days. Jan has 31 days. So the last column-number will change from month to month. So, if Iam storing "1" in B1 for the month of Febuary 2003, the last column will be AC1 , which will store the value 28. The screen look like:

NAME 1.......2..............28
EMP1 L........................L.......*
EMP2 L L L..........

So, 1,2....28 represent the days. A, B....AD represent the columns. "L" represents that the employee took a leave. FOr example, EMP1 took leave on 1st and 28th of Feb. NOw , in AD2(where u see the *), I want to store the total number of days that an employee was on leave. SO there should be a sum function for this. How can I write this. Becoz, u should write a loop like this:

Starting from B2 to the last column find the total number of "L". Put that in AD2. This is for the first employee. Repeat this process for all the employees.

Can someone help me with the VBA code?
I wrote it and Iam not able to get it.

Thank u

ok i have looked around the internet and these forums for a solution to this problem ...But havent found one anywhere..So i am hoping some techy can give me a pointer on how to solve it ..

I have a workbook that i use for sorting holidays fo employees..each sheet is a year claender that i put in letter for the reason they are not in ie H = Holiday or S = Sickness ...i have another sheet that counts everyones Holidays up so that no more than 3 peolpe can have the same day off ...So the prob is that i want a error message to pop up when im putting a H into a persons timesheet ...when the overall sheet reaches above 3 i cant use validation as the cell thats going to tell me its over 3 isnt the one im using and also it calcs it self ...

Any ideas and if that doesnt make sence just say i could even email anyone the workbook to have a look if that would help

thanx in advnace

also would like to make a log that records all activity inside the workbook

No luck finding an answer? You could always try Google.