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.

How to Create Monthly Attendance Sheet in Excel with Formula: with Easy Steps

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.

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.

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.

Download Practice Workbook

You can download the practice workbook from here.

Monthly Attendance Sheet.xlsx

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. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

<< Go Back to Employee Attendance Sheet Excel | Excel HR Templates | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Sam Jul 4, 2023 at 9:23 PM

I made this tracker, however, whenever I add ‘A’ or ‘P’ it doesn’t change with the month, it just stays in the specific cell.

Afia Kona Jul 10, 2023 at 10:21 AM

Dear Sam,
Thank you for your comment. I understand that you are looking for a more dynamic Attendance Sheet, if so, you can use the following VBA code as an update:


Private Sub Worksheet_Change(ByVal Target As Range)

'Developed by ExcelDemy

If Target.Address = "$C$4" Or Target.Address = "$C$5" Then

    totalDays = Array(31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)
    
    If Range("C5").Value Mod 4 = 0 Then
        totalDays(1) = 29
    End If

    Dim specificDate As Date
    Dim dayOfWeek As Integer
    Dim dayName As String
    
    
    monthNumber = Month(DateValue(Range("C4").Value & " 1 2000"))
    yearNumber = Range("C5").Value
    
    Range(Range("D7").Cells(1, 1), Range("D7").Cells(6, 31 + 2)).ClearContents
    Range(Range("D7").Cells(1, 29), Range("D7").Cells(6, 31 + 2)).ClearFormats
    
    For i = 1 To totalDays(monthNumber - 1)
    
    ' Specify the date
        specificDate = DateSerial(yearNumber, monthNumber, i)

    ' Get the day of the week as a number (1 for Sunday, 2 for Monday, etc.)
        dayOfWeek = Weekday(specificDate, vbSunday)

    ' Get the name of the day of the week
        dayName = WeekdayName(dayOfWeek, False, vbSunday)

    ' Display the name of the day
        Range("D7").Cells(1, i) = i
        Range("D8").Cells(1, i) = Left(dayName, 3)
         
    Next i
    
    Range("D7:D12").Copy
    For i = 29 To totalDays(monthNumber - 1)
        Range("D7:D12").Cells(1, i).PasteSpecial Paste:=xlPasteFormats
        Range("D7:D12").Cells(1, i).ColumnWidth = Range("D7").Cells(1, 1).ColumnWidth
    Next i
    
    Application.CutCopyMode = False
    
    Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 1) = "Present"
    Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 1).Interior.Color = vbGreen
    Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 1).ColumnWidth = 8
    Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 1).Font.Bold = True
    Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 1).HorizontalAlignment = xlHAlignCenter
    Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 1).VerticalAlignment = xlVAlignCenter
    
    Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 2) = "Absent"
    Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 2).Interior.Color = vbGreen
    Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 2).ColumnWidth = 8
    Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 2).Font.Bold = True
    Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 2).HorizontalAlignment = xlHAlignCenter
    Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 2).VerticalAlignment = xlVAlignCenter
    
    For j = 1 To 2
        For k = 1 To 5
            With Range("D7:D12").Cells(2, totalDays(monthNumber - 1) + 1).Cells(k, j)
                .Borders(xlEdgeTop).LineStyle = xlContinuous
                .Borders(xlEdgeTop).Color = RGB(0, 0, 0)
                .Borders(xlEdgeTop).Weight = xlThin
    
                .Borders(xlEdgeBottom).LineStyle = xlContinuous
                .Borders(xlEdgeBottom).Color = RGB(0, 0, 0)
                .Borders(xlEdgeBottom).Weight = xlThin
    
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Color = RGB(0, 0, 0)
                .Borders(xlEdgeLeft).Weight = xlThin
    
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Color = RGB(0, 0, 0)
                .Borders(xlEdgeRight).Weight = xlThin
            End With
        Next k
    Next j
End If
End Sub

Download this Excel file for a better understanding.
I hope that your problem will be solved now. If you have any further issue, please let us know in the comment section.

Best
Afia Aziz Kona
Excel and VBA Content Developer
Exceldemy