How to Create a Monthly Staff Attendance Sheet in Excel

If you want to make a monthly staff attendance sheet, Excel can be really handy. The main objective of this article is to explain how you can create a monthly staff attendance sheet in Excel.

How to Create a Monthly Staff Attendance Sheet in Excel: with Easy Ways

To explain this article, I have taken the following dataset. This dataset contains information about employees. I will show you how you can make a monthly staff attendance sheet in Excel in 7 easy steps using this dataset.

Step-01: Creating Month Menu

In this first step, I will explain how you can create a month menu for your monthly attendance sheet in Excel.

Firstly, select the cell where you want your month to appear. Here, I selected cell C4.
Secondly, go to the Data tab.
Thirdly, select Data Tools.

Now, you will see a drop-down menu.

After that, select Data Validation from the drop-down menu.

Here, you will see a dialog box named Data Validation will appear.

Firstly, select the drop-down option for Allow.

Secondly, select List from the drop-down menu.

After that, write all the twelve months’ names as the Source.
Next, select OK.

Now, you will see that you have got a drop-down option for selecting a month in your Excel sheet.

Firstly, click on the drop-down option.
Secondly, select your month. Here, I selected July.

Now, by following the previous steps open the Data Validation dialog box for selecting the year.

Firstly, select List as Allow.
Secondly, write the years as the Source.
Thirdly, select OK.

Here, you will see that you have got a drop-down option for selecting the year in your Excel sheet.

Firstly, click on the drop-down option.
Secondly, select your year. Here, I selected 2022.

Now, I will define the first and last date of the month.

Firstly, select the cell where you want your first day of the month. Here, I selected cell C7.

Secondly, in cell C7 write the following formula.

=DATEVALUE("1"&C4&C5)

Here, I selected “1”, C4, and C5 as date_text in the DATEVALUE function. The formula will return the first day of our selected month and year.

Finally, press ENTER and you will get your Date.

After that, select the cell where you want your last day of the month. Here, I selected cell E7.
Next, in cell E7 write the following formula.

=EOMONTH(C7,0)

Here, in the EOMONTH function, I selected C7 as start_date and 0 as months. The function will return the last day of the current month.

Finally, press ENTER.

Step-02: Inserting Dates into Monthly Staff Attendance Sheet in Excel

In this step, I will show you how you can insert dates into your monthly staff attendance sheet in Excel.

Firstly, select the cell where you want the first date. Here, I selected cell F9.
Secondly, in cell F9 write the following formula.

=C7

Here, I selected the value in cell C7 as the value in cell F9 which is the first day of the month.

Finally, press ENTER and you will get your date.

Now, I will change to format of the date.

Firstly, right-click on the date.
Secondly, select Format Cells.

Here, the Format Cells dialog box will appear.

Firstly, select Custom.
Secondly, write the custom format Type you want. Here, I wrote the format I want.
Thirdly, select OK.

Now, you can see that I have got the format I wanted.

After that, select the next cell.
Then, in that cell write the following formula.

=IF(F9<$E$7,F9+1,"")

Here, in the IF function, I selected F9<$E$7 as logical_test, F9+1 as value_if_true, and blank as value_if_false. This formula will return the next date until it reaches the last date of the month.

Finally, press ENTER to get the result.

Now, drag the Fill Handle to copy the formula.

Here, you can see that I have copied the formula.

I copied it till I reached the last date of my month.

Step-03: Inserting Weekdays into Monthly Staff Attendance Sheet in Excel

In this step, I will show you how you can add weekdays to your monthly staff attendance sheet in Excel.

Firstly, select the cell where you want your first weekday of the month. Here, I selected cell F10.
Secondly, in cell F10 write the following formula.

=TEXT(F9,"ddd")

Here, in the TEXT function, I selected F9 as value and “ddd” as format_text. The formula will return the value in cell F9 in the given format.

Finally, press ENTER and you will get your weekday.

Now, drag the Fill Handle to copy the formula.

Here, you can see that I have copied the formula.

I copied it till I reach the last date of my month.

Step-04: Highlighting Weekly Holidays in Monthly Staff Attendance Sheet in Excel

Here, I will show you how you can highlight the weekly holidays in the monthly staff attendance sheet in Excel.

Firstly, select the first cell from where you want to highlight the holidays,
Secondly, go to the Home tab.
Thirdly, select Conditional Formatting.

Now, a drop-down menu will appear.

After that, select New Rule.

Here, a dialog box named New Formatting Rule will appear.

Firstly, select Use a formula to determine which cells to format.
Secondly, write the formula for Format values where this formula is true. Here, I wrote the following formula.

=F$10=”Sun”

Here, the formula will check Row 10 for the value “Sun” and format cells if the value is true.

Thirdly, select Format.

Now, the Format Cells dialog box will appear.

Firstly, go to the Fill tab.
Secondly, select the color you want.
Thirdly, select OK.

Here, the format will be added to the New Formatting Rule dialog box.

Finally, select OK.

At this point, you will not see any change yet.

Firstly, select the cell you selected before.
Secondly, select Format Painter.

Now, drag the Fill handle and apply the formatting to your entire dataset.

Here, you can see that I have applied the formatting to my entire dataset.

Now, you can see that I have highlighted the weekly holidays in my monthly staff attendance sheet.

I have highlighted all Sundays in the whole month.

Step-05: Creating Input Attendance in Monthly Staff Attendance Sheet in Excel

In this section, I will show you how you can create input attendance in your monthly staff attendance sheet.

Firstly, select the cell where you want your input attendance.
Secondly, go to the Data tab.
Thirdly, select Data Tools.

Now, you will see a drop-down menu.

After that, select Data Validation from the drop-down menu.

Here, you will see a dialog box named Data Validation will appear.

Firstly, select the drop-down option for Allow.

Secondly, select List from the drop-down menu.

After that, write P and A as the Source. Here, P stands for present and A stands for absent.
Next, select OK.

Now, you will see that you have got a drop-down option for input attendance in your Excel sheet.

After that, drag the Fill Handle to copy this Data Validation throughout the whole attendance sheet.

Firstly, select the cell where you want your input attendance. Here, I selected cell F11.
Secondly, click on the drop-down option.
Thirdly, select P if the person is present and A if the person is absent.

Now, you can see I have input attendance for the employee named Adam for July 1, 2022.

Here, I filled up the whole attendance sheet.

I filled up unlit I reach the last date of my month.

Step-06: Counting Total Working Days

In this step, I will explain how you can count total Present and Absent days in a monthly staff attendance sheet.

Firstly, select the cell where you want to count your total Present days. Here, I selected cell AK11.
Secondly, in cell AK11 write the following formula.

=COUNTIF(F11:AJ11,"P")

Here, in the COUNTIF function, I selected F11:AJ11 as the range and “P” as the criteria. The formula will count if the cells in the selected range contain “P”. And, thus it will return the total Present days.

Finally, press ENTER to get the result.

Now, drag the Fill Handle to copy the formula.

In the following picture, you can see that I have copied my formula for all the cells.

Now, I will count the total Absent days.

Firstly, select the cell where you want to count your total Absent days. Here, I selected cell AL11.
Secondly, in cell AL11 write the following formula.

=COUNTIF(F11:AJ11,"A")

Here, in the COUNTIF function, I selected F11:AJ11 as the range and “A” as the criteria. The formula will count if the cells in the selected range contain “A”. And, thus it will return the total Absent days.

Finally, press ENTER to get the result.

Now, drag the Fill Handle to copy the formula.

In the following picture, you can see that I have copied my formula for all the cells.

Step-07: Saving Excel File as Template

Here, I will show you how you can save this Excel file as a template.

Firstly, go to the File tab.

Secondly, select Save As.
Thirdly, select Browse.

Now, you will see a dialog box named Save As will appear.

Firstly, select the drop-down option to select Save as type.

Secondly, select Excel Template from the drop-down menu.

Finally, select Save.

Now, your file will be saved as a template and you will be able to access it while creating a new Excel file.

Practice Section

Here, I have provided a practice sheet for you to practice creating a monthly staff attendance sheet.

Download Practice Workbook

Monthly Staff Attandance Sheet.xlsx

Conclusion

To conclude, in this article I tried to cover how you can create a monthly staff attendance sheet in Excel with 7 easy steps. I hope, this article was helpful for you. If you have any questions, feel free to let me know in the comment section below.

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

Get FREE Advanced Excel Exercises with Solutions!

4 Comments

siknader Aug 10, 2022 at 5:46 PM

unable to find datevalue for FEBURARY rest is working please do the needful.
- Reply
  Shamima Aug 21, 2022 at 2:48 PM
  
  Hi Sikander,
  Hope you are doing well.
  To get the DateValue for February, click on the drop-down option of Month and then select February.
  
  Then you will get the DateValue and the rest will be updated automatically.
  
  Note: If you want to type the month name in that cell you have to be careful with the spelling of the month name.
  
  Thanks
  
  Regards
  Shamima Sultana
Tudor Apr 19, 2023 at 9:22 PM

Hello, very well explained, but I have two questions:
1. How can I change the month and day from English to Romanian?
2. If an employee works on Saturday and/or Sunday, how can I collect those weekend hours separately, in a cell?
Thank You!
- Reply
  Shamima Sultana Apr 26, 2023 at 11:46 AM
  
  Dear Tudor,
  
  Hope you are doing well. Answer of your questions are given below with explanation.
  
  1. How can I change the month and day from English to Romanian?
  
  Select the cell with month and date, cell C7 in our case.
  Right-click with the mouse and select Format Cells.
  
  In the Format Cells window, select Romanian (Moldova) under the Locale menu.
  Then, select any type under the Type menu and click the OK button.
  
  The month and date are changed into Romanian from English. Do the same process for cell E7.
  
  2. If an employee works on Saturday and/or Sunday, how can I collect those weekend hours separately, in a cell?
  
  Let’s say the first employee works on Saturday and Sunday for 5 and 6 hours respectively. He works on 4 weekends which you can see in the picture. To sum these values we’ll use the SUMIFS function.
  Apply the formula in any cell where you want the weekend hours, let’s say cell AM11-
  =SUMIFS($F$11:$AJ$11,$F$11:$AJ$11,"<>P",$F$11:$AJ$11,"<>A")
  
  After pressing Enter you’ll get the total weekend hours. You can use the Fill Handle tool to apply this formula to other employees.