How to make summary of attendance statistics table in excel

Production steps:

First, as shown below, leave the first three lines blank. Enter a name in A4 and add a border starting from A4 to H4.

Select A1:H1 and select [Merge Cells].

Double-click the merged cell and enter the formula: =TEXT(NOW(),"yyyy year mm monthly attendance sheet")

Note: In order To prevent the function from changing automatically in June, you can paste it into a numerical value and save it at the end of the month.

Enter 1 in B3 and fill in the date to the right. In this example, fill in 7 days, which is the 1st to 7th of the month.

Note: The actual situation can be filled to 31 and judged by IF. If it exceeds the maximum number of days in this month, it will be blank.

Enter the first day of the month in B2 and fill to the right.

Note: This can also be done using the time formula. If the month and year are generated using drop-down menus, both here and the table header can be automatically generated using formulas and will not change with time.

Select the date you just entered, right-click and select [Format Cells]. Enter:

aaaa in [Custom], and click the [OK] button.

#In this way, the date just entered will be displayed in the "day of the week" display style.

Enter the mark for attendance in an unused blank column, then enter [Option] in the name box and press Enter. This defines the name of an "option".

Related recommendations: "excel basic tutorial"

Select the blank space to the right of the name and select [Data]-[Data Effectiveness].

Select "Sequence" in [Allow], enter: = option in Source, and check [Provide drop-down arrow].

In this way, a drop-down menu is generated in columns B to H, which can mark employee attendance status.

Choose drop-down menus on different days based on the actual attendance of employees. The completed effect is as follows:

##Finally The first step is to make statistics on attendance for the month.

Set the statistical format on the right side of the recording area as shown below. Among them, I4:N4 needs to enter formula statistics.

Double-click the I4 cell and enter the formula: =COUNTIF($B4:$H4,I$3)

Note: The original range and table in the formula Header references are of particular concern.

Then pull the formula to the right to return the employee’s attendance statistics for the current month.

The above is the detailed content of How to make summary of attendance statistics table in excel. For more information, please follow other related articles on the PHP Chinese website!