Days worked over a period

You can run a report to show the total number of hours worked by an employee each month.

To do this, run the queries below, and collate them into a work book.

• Employee Listing- Captures all employees, using Available Working Minutes and Available Working Daysfrom ABSENCE (CALCULATED).

• Monthly Absence Hours- Using fields from ABSENCE (CALCULATED).

• Monthly Holiday Hours- Using Duration Minutes from HOLIDAY (the formula in the workbook divides by 60 to make this into hours).

• Monthly Lieu Time.

Once you have the sheets in a workbook, add SUMIF formulas, then add a formula for each person to give their Total Working Hours.

You can also add in overtime hours to give the Total Working Hours for the month, or any period of time.

The formulas for the 3 columns are:

Absence Hours	Holiday Hours	Total Working Hours
=SUMIF('Absence Data Jan 23'!$A$2:$A$167,Jan 23 Analysis'!A2,'Absence Data Jan 23'!$I$2:$I$167)	=SUMIF('Holiday Data Jan 23'!$A$2:$A$143,Jan 23 Analysis'!A2,'Holiday Data Jan 23'!$I$2:$I$143)/60	=(D2/60)-(F2+G2)
The formulas are on the Employee Listing page, since the other sheets will not have every employee listed.		D = available minutesF = Absence HoursG = Holiday Hours