How to Create Monthly Attendance Sheet in Excel with Formula

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

Procedures to Create Monthly Attendance Sheet

  • Next, open another sheet and type all the months there.

Procedures to Create Monthly Attendance Sheet

  • 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.

Procedures to Create Monthly Attendance Sheet

  • 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.

Procedures to Create Monthly Attendance Sheet

  • Afterward, you will find the following result.

Procedures to Create Monthly Attendance Sheet

  • Then, select another blank cell( in this case C5 cell) and insert the following formula:
=year

Procedures to Create Monthly Attendance Sheet

  • 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.

Procedures to Create Monthly Attendance Sheet

  • Finally, you will get a result similar to the following image.

Procedures to Create Monthly Attendance Sheet

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)

Procedures to Create Monthly Attendance Sheet

  • Second, press enters and you will find the below result.

Procedures to Create Monthly Attendance Sheet

  • Third, go to another blank cell and insert the following formula:
=EOMONTH(E4,0)

Procedures to Create Monthly Attendance Sheet

  • Last, you will get the desired result like the below image.

Procedures to Create Monthly Attendance Sheet


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.

Procedures to Create Monthly Attendance Sheet

  • Thirdly, Right-click on the cell and select the Format Cells option.

Procedures to Create Monthly Attendance Sheet

  • Fourthly, in the Format Cells window, go to the Custom option and type dd in the Type option, and press OK.

Procedures to Create Monthly Attendance Sheet

  • After that, you will get the below result.

Procedures to Create Monthly Attendance Sheet

  • 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,””)

Procedures to Create Monthly Attendance Sheet

  • Then, you will get this result.

Procedures to Create Monthly Attendance Sheet

  • Lastly, use the Fill Handle to fill in all the dates of the month.

Procedures to Create Monthly Attendance Sheet


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.

Procedures to Create Monthly Attendance Sheet

  • Finally, use Fill Handle to get all the days of the month.

Procedures to Create Monthly Attendance Sheet


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.

Procedures to Create Monthly Attendance Sheet

  • Afterward, in the New Formatting Rule window box, select the desired cell in the format value box:
=D$9="Sun"

Procedures to Create Monthly Attendance Sheet

  • 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.

Procedures to Create Monthly Attendance Sheet

  • 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.

Procedures to Create Monthly Attendance Sheet

  • Finally, you will get results similar to the below image.

Procedures to Create Monthly Attendance Sheet


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.

Procedures to Create Monthly Attendance Sheet

  • 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

Zehad Rian Jim

Zehad Rian Jim

Hi there! I am Zehad Rian Jim. I graduated with a bachelor's degree in engineering from BUET. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include watching movies, tv series, and meeting new people. I also enjoy sports. My favorite sports are Cricket (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo