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.


Download Practice Workbook


7 Easy Steps to Create a Monthly Staff Attendance Sheet in Excel

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.

7 Easy Steps to Create a Monthly Staff Attendance Sheet in Excel


Step-01: Creating Month Menu

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

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

Creating Month 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.

Creating Month 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.

Creating Month Menu

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

Creating Month Menu

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.

Creating Month Menu

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.

Creating Month Menu

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

Read More: How to Create Monthly Attendance Sheet in Excel with Formula


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

Inserting Dates into Monthly Staff Attendance Sheet in Excel

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.

Inserting Dates into Monthly Staff Attendance Sheet in Excel

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,"")

Inserting Dates into Monthly Staff Attendance Sheet in Excel

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.

Inserting Dates into Monthly Staff Attendance Sheet in Excel

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

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

Inserting Dates into Monthly Staff Attendance Sheet in Excel

Read More: How to Create Employee Attendance Sheet with Time in Excel


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")

Inserting Weekdays into Monthly Staff Attendance Sheet in Excel

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.

Inserting Weekdays into Monthly Staff Attendance Sheet in Excel

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

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

Inserting Weekdays into Monthly Staff Attendance Sheet in Excel

Read More: How to Make Daily Attendance Sheet in Excel (2 Effective Ways)


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.

Highlighting Weekly Holidays in Monthly Staff Attendance Sheet in Excel

Now, a drop-down menu will appear.

  • After that, select New Rule.

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

Highlighting Weekly Holidays in Monthly Staff Attendance Sheet in Excel

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

Highlighting Weekly Holidays in Monthly Staff Attendance Sheet in Excel

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

Highlighting Weekly Holidays in Monthly Staff Attendance Sheet in Excel

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.

Highlighting Weekly Holidays in Monthly Staff Attendance Sheet in Excel

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

Highlighting Weekly Holidays in Monthly Staff Attendance Sheet in Excel

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.

Read More: Attendance and Overtime Calculation Sheet in Excel


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.

Creating Input Attendance in Monthly Staff Attendance Sheet in Excel

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.

Creating Input Attendance in Monthly Staff Attendance Sheet in Excel

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

Creating Input Attendance in Monthly Staff Attendance Sheet in Excel

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

Creating Input Attendance in Monthly Staff Attendance Sheet in Excel

Here, I filled up the whole attendance sheet.

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

Creating Input Attendance in Monthly Staff Attendance Sheet in Excel

Read More: How to Track Attendance in Excel (with Detailed Steps)


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")

Counting Total Working Days

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.

Counting Total Working Days

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.

Counting Total Working Days

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

Counting Total Working Days

Read More: Attendance Sheet in Excel with Formula for Half Day (3 Examples)


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.

Saving Excel File as Template

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

Saving Excel File as Template

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

Read More: Make Automated Attendance Sheet in Excel (With Easy Steps)


Practice Section

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


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.


Related Articles

Mashhura

Mashhura

Hey! Welcome to my profile. Right now, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

2 Comments
  1. unable to find datevalue for FEBURARY rest is working please do the needful.

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

      monthly staff attendance

      Then you will get the DateValue and the rest will be updated automatically.

      2. Monthly Stuff Attendance

      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

Leave a reply

ExcelDemy
Logo