Zehad Rian Jim
This tutorial will demonstrate the steps monthly attendance sheet in excel with a formula. In any company or educational institution, it is very important to track records of the monthly attendance of their employees or students. It helps to understand the regularity of students or employees on a day-to-day basis every month. Moreover, this sheet can be helpful for payroll purposes as well. This article will guide you in every step of creating an effective monthly attendance sheet.
Download Practice Workbook
You can download the practice workbook from here.
Step-by-Step Procedures to Create Monthly Attendance Sheet in Excel with Formula
Our goal is to create a monthly attendance sheet. To fulfill that, we will follow the 8 steps as described below. If you follow the steps correctly then you should learn how to on your own create a monthly attendance sheet. The steps are:
1. Creating Month and Year Menu
At the very beginning, our job is to create a Month and Year menu so that anyone can understand the monthly attendance for which month of the year. The description of this step is given below.
- First, go to any cell (in this case C4 cell) and insert the following formula:
=Month
- Next, open another sheet and type all the months there.
- After that, return to the first worksheet and select the cell you have put the formula before.
- Then, go to the Data tab and select the Data Validation option.
- Furthermore, the Data Validation window will open on your screen.
- Next, go to the Settings option and select the List in the Allow tab.
- Then, choose the list of the months in another worksheet in the Source option and press OK.
- Afterward, you will find the following result.
- Then, select another blank cell( in this case C5 cell) and insert the following formula:
=year
- Next to that, repeat the same steps that were performed for the month Select Cell > Data > Data Validation.
- Then, again the Data Validation window will open on the screen. But this time put 2020,2021,2022 in the Source for making the steps easy and press OK.
- Finally, you will get a result similar to the following image.
Read More: Attendance and Overtime Calculation Sheet in Excel
2. Input Start and End Date of Month
Next, our target is to input the start and date of the month we want to record. The step is described below.
- At first, go to any blank cell and insert the following formula:
=DATEVALUE( "1" &C4 &C5)
- Second, press enters and you will find the below result.
- Third, go to another blank cell and insert the following formula:
=EOMONTH(E4,0)
- Last, you will get the desired result like the below image.
3. Inserting Dates Using IF Function
We will aim to insert dates using the IF Function in this step. The step is described below.
- Firstly, list the people with their names. In this case, we have Serial No. in the B7 cell and Name in the C7 cell.
- Secondly, select any blank cell and refer to the starting date (in this case =E4) you have created in the previous step.
- Thirdly, Right-click on the cell and select the Format Cells option.
- Fourthly, in the Format Cells window, go to the Custom option and type dd in the Type option, and press OK.
- After that, you will get the below result.
- In the next step, select the blank side cell of the previous cell and insert the following formula:
=IF(D7<$H$4+1,D7+1,””)
- Then, you will get this result.
- Lastly, use the Fill Handle to fill in all the dates of the month.
4. Utilizing TEXT Function to Input Days
By utilizing the TEXT Function now we will input the days of the month. The description of this step is.
- First, select the below cell of the first date( in this case cell D7) and insert the following formula.
=TEXT(D7,"ddd")
- Next, you will get the desired result.
- Finally, use Fill Handle to get all the days of the month.
5. Highlighting Sundays in Worksheet
Now, our target is to highlight the weekends( in this case Sundays). For that, we will do as described below.
- At first, select the cell below the below cell of the first day of the month and then go to the Home tab.
- Then, go to the Conditional Formatting and select the New Rule option.
- Afterward, in the New Formatting Rule window box, select the desired cell in the format value box:
=D$9="Sun"
- In the next step, if you the Format option in the previous step then the Format Cells will appear on the screen.
- Select the desired color from the Fill tab and press OK.
- Then, select the cell you have used for the conditional formatting.
- Next, select the Format Painter option and use Fill Handle to select all the cells you want this condition to apply to.
- Finally, you will get results similar to the below image.
6. Restricting Data Entry on Weekend
Our new aim is to restrict data entry on the weekends. For that, we will perform as the below description.
- To begin with, select the first day of the month (in this case cell D8) and choose Data Validation from the Data tab.
- In addition, select Custom in the Allow section and put the following formula in the Formula option in the Setting tab of the Data Validation option.
=D$8<>"Sun"- Then press OK.
- Furthermore, in the Error Alert tab, choose Title and Error message according to your wish and press OK.
- Next to that, apply this to all desired cells using Fill Handle.
- Finally, if you try to enter any data on Sundays then you will get the below result.
7. Tracking Present and Absent Days
We want to track present and absent days just by seeing them with the COUNTIF Function. The process of this step is.
- First, enter the data of every person and create Present and Absent headings in two cells.
- Next, in the first of the Present heading, enter the following formula.
=COUNTIF(D9:AE9,"P")
- After that, go to the first cell of the Absent heading and insert the following formula:
=COUNTIF(D9:AE9,"A")
- Finally, use Fill Handle to apply these formulas to the desired cells and after that, you will get the below result.
Read More: Attendance Sheet in Excel with Formula for Half Day (3 Examples)
8. Saving Desired File as Template
At last, our target is to save the whole datasheet as a Template file. In this step, we will choose the File option and press Save As option, and save the file with the desired name.
Things to Remember
- As it has a very long process, we should be careful about which cell you are working on. If you choose the wrong cell in any step, it can create a very messy situation.
- It is recommended that while following the steps, have our excel files side by side. If any confusion happens, you can go to the excel file to understand it well.
- You have to insert present and absent individuals manually. Only after that, the COUNTIF function will work.
Conclusion
Henceforth, follow the above-described methods. Thus, you will be able to create a monthly attendance sheet in excel with a formula. Let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- How to Create Employee Attendance Sheet with Time in Excel
- How to Create Student Attendance Sheet in Excel with Formula
- How to Make Daily Attendance Sheet in Excel (2 Effective Ways)
- How to Make Time Attendance Sheet in Excel (2 Easy Ways)
- QR Code Attendance Tracking with Excel (with Easy Steps)
- Attendance Sheet with Salary in Excel Format (with Easy Steps)
