To allow users to only write p or a for present and absent respectively we can use data validation.

Present and absent formula in excel.

The function will return true which means that the value in cell e2 is present in the range a2 c6. For example if the absent streak is 1 the present streak should be 0. Except for 9 2 19 the two values will never both equal 0. Month days will be updated. So the formula reduces like this. Select list from options and write a p in the text box.

Copy the same formula by pressing the key ctrl c and paste in the range f3 f10 by pressing the key ctrl v on your keyboard. On the other hand the absent streak should 0 when the present streak is to 1. Write the formula and countif a 2 c 6 e 2 e 10 press ctrl shift enteron the keyboard. If the absent streak is 2 the present streak should still be 0. Countif c5 l5 x count present. Before using this template please read following points.

If the returned value is a number then the employee was present else absent. The count absent days the worksheet uses countif again configured to count empty cells. When formatted using the percentage number format 08 8. Follow the below given steps select the cell f2. This attendance sheet in excel with formula will help in leave and attendance management of employees with visualization dashboard. We need to check the presence of the values in column e in the columns a c.

The formula in f5 first calculates the number of students absent using c6 d6 then divides the result by the total students. This formula simply uses countif with a criteria of x not quotation marks to count x s in each row where x represents present and an empty cell represents absent. This template can be used for monitoring the attendance and leave for one month. Select any cell go to data in ribbon and click on data validation. You have to update year and moth in the sheet. Use the match formula against the date column last column in your data which represent check in date and against the employee name column.