How to Create a Monthly Attendance Sheet in Excel Using Formula: 8 Steps

Step 1 – Creating a Month and Year Menu

  • Go to any cell (i.e. C4) and insert the following formula:
=Month

Procedures to Create Monthly Attendance Sheet

  • Open another sheet and enter all the months.

Procedures to Create Monthly Attendance Sheet

  • Return to the first worksheet and select the cell where you previously put the formula.
  • Go to the Data tab and select Data Validation.

Procedures to Create Monthly Attendance Sheet

  • The Data Validation window opens.
  • Go to Settings and select List in the Allow tab.
  • Choose the list of the months in another worksheet in the Source option and press OK.

Procedures to Create Monthly Attendance Sheet

  • You will see the following result.

Procedures to Create Monthly Attendance Sheet

  • Select another blank cell(i.e. C5) and enter the following formula:
=year

Procedures to Create Monthly Attendance Sheet

  • In the next cell, repeat the same steps that were performed for the month Select Cell > Data > Data Validation.
  • The Data Validation window opens. Enter 2020,2021,2022 in the Source and press OK.

Procedures to Create Monthly Attendance Sheet

  • 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


Step 2 – Inputting a Start and End Date of the Month

  • Go to any blank cell and insert the following formula:
=DATEVALUE( "1" &C4 &C5)

Procedures to Create Monthly Attendance Sheet

  • Press enter to find the below result.

Procedures to Create Monthly Attendance Sheet

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

Procedures to Create Monthly Attendance Sheet

  • You will get a result similar to the following image.

Procedures to Create Monthly Attendance Sheet


Step 3 – Inserting Dates Using IF Function.

  • Enter the people’s names. (We have Serial No. in cell B7 and Name in cell C7).
  • Select any blank cell and refer to the starting date (in this case =E4) you created in the previous step.

Procedures to Create Monthly Attendance Sheet

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

Procedures to Create Monthly Attendance Sheet

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

Procedures to Create Monthly Attendance Sheet

  • You will get the below result.

Procedures to Create Monthly Attendance Sheet

  • Select the blank cell next to the previous cell and insert the following formula:
=IF(D7<$H$4+1,D7+1,””)

Procedures to Create Monthly Attendance Sheet

  • You will get this result.

Procedures to Create Monthly Attendance Sheet

  • Use Fill Handle to fill in all the dates of the month.

Procedures to Create Monthly Attendance Sheet


Step 4 – Utilizing TEXT Function to Input Days

  • Select the cell below the first date (i.e. cell D7) and insert the following formula:
=TEXT(D7,"ddd")

  • You will get the desired result.

Procedures to Create Monthly Attendance Sheet

  • Use Fill Handle to get all the days of the month.

Procedures to Create Monthly Attendance Sheet


Step 5 – Highlighting Sundays in the Worksheet

  • Select the cell below the first day of the month and then go to the Home tab.
  • Go to Conditional Formatting and select the New Rule option.

Procedures to Create Monthly Attendance Sheet

  • 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

  • If you chose the Format option in the previous step, 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

  • Select the cell you have used for the conditional formatting.
  • Select the Format Painter option and use Fill Handle to select all the cells to which you want this condition to apply.

Procedures to Create Monthly Attendance Sheet

  • You will get results similar to the below image.

Procedures to Create Monthly Attendance Sheet


Step 6 – Restricting Data Entry on a Weekend

  • Select the first day of the month (i.e. cell D8) and choose Data Validation from the Data tab.

  • Select Custom in the Allow section.
  • Click Formula in the Setting tab under Data Validation.
  • Enter the following formula:
=D$8<>"Sun"
  • Press OK.

  • In the Error Alert tab, choose a Title and Error message and press OK.

  • Apply this to all desired cells using the Fill Handle.

  • You will get the below result.


Step 7 – Tracking Present and Absent Days

  • Enter the data of all names and create Present and Absent headings in two cells.

Procedures to Create Monthly Attendance Sheet

  • In the first cell under the Present heading, enter the following formula:
=COUNTIF(D9:AE9,"P")

  • In the first cell under the Absent heading, insert the following formula:
=COUNTIF(D9:AE9,"A")

  • Use the Fill Handle to apply these formulas to the desired cells to get the below result.

Read More: How to Create a Monthly Staff Attendance Sheet in Excel


Step 8 – Saving a Desired File as a Template

  • You want to save the whole datasheet as a Template file.
  • Choose the File option and press Save As. Save the file with the desired name.

Read More: Attendance Sheet in Excel with Formula for Half Day


Download the Practice Workbook

You can download the practice workbook from here.


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Zehad Rian Jim
Zehad Rian Jim

Zehad Rian Jim is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He's good with Microsoft Office and has written more than 80 helpful articles for ExcelDemy in the last year. Now, he makes fun and easy-to-follow Excel tutorials for YouTube as part of the ExcelDemy Video project. Zehad loves figuring out Excel problems, and his passion for learning new things in Excel shines through in... Read Full Bio

2 Comments
  1. 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.

    • 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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo