Steps to create a fully automated attendance sheet in Excel
Let’s get started with creating our first automated turnout sheet in Excel.
The first step is to create a Month menu in the sheet. You will choose a month from the drop-down menu and start marking the attendance.
Here are the steps to create the Month menu.
- Start with creating a heading named “Attendance Sheet”
- Type “Month” in the upper cells in the sheet.
- Create a new sheet and prepare a list of all months.
- Go back to the sheet with the attendance sheet.
- Click in the adjacent blank cell to the Month cell.
- Go to the Data tab.
- Click on Data Validation.
- Select List in the Allow menu in the Data Validation window.
- Click in Source and go to the sheet with month names.
- Select the month names list and click OK.
The Month menu is now ready!
The next step is to create the Year menu. The steps are the same as above, but you can make your task simpler by typing three or four consecutive years separated with commas in the Source box in the Data validation window.
The years will get added to the drop-down menu.
The next step is to add the first day and last day of the month above the attendance sheet.
- Skip two cells after the month name cell and type the DATEVALUE formula.
- Type =DATEVALUE(“1”& select the month name, put the & sign and select the year.
- Press ENTER, and you will see the date in the number format.
- Change the format by right-clicking on that cell and pressing Format Cells.
- Go to the Date tab and choose the date format you like for dates.
- Click OK.
You can see that the date will display in the desired format.
As we change the month, the first day gets updated in the cell.
Now to display the appropriate last date of every month in the next cell, follow these steps.
- We will use the EOMONTH formula to denote the end of the month.
- Type =EOMONTH( select the first date we just created, put a comma, and type 0.
- Press ENTER, and you will get the date in a number format again. Change the formatting by clicking Format Cells.
- Or, apply the formatting of the first date cell using the Format Painter tool on the last date cell.
You can see that the first and last dates will show appropriately. Change the month from the drop-down list, and these dates will adjust automatically.
It is time to construct the actual attendance sheet in the worksheet. Let’s get started!
- Create the Name and the Sr. no columns first. You can add custom fields like Mobile or email etc.
Let’s begin adding the dates in the top rows of the table.
- Click on the cell where you want the dates to begin.
- Type = and select the cell with the first date we learned to create in the earlier steps.
- Press ENTER to get identical.
- Right-click on the new date and click on Format Cells.
- Go to the Custom tab.
- Click in the Type field and type “dd”
- Click OK.
You will see that the date displays as “01”.
Now, we will find the second day of the month.
Don’t worry, you don’t have to find every date of the month manually. The effort is only for the first two days, and then you only have to drag the cell for further dates.
Follow the steps to find day 2 in the sheet.
- Click on the adjacent blank cell of the first date.
- Type =if(firstday<lastday+1,firstday+1,””). This is an illustration of which cells you have to use.
- Click on the cell address of the last date and press F4 or Fn+F4 to lock the cell address. You should see two dollar signs getting added to the cell address which indicates that the cell address is locked.
This formula says that if the first date is less than the last date, and then add the next day. If not, leave it blank (“”).
- Close the brackets and press ENTER.
You will see the number code again, so use the format painter tool to copy formatting from the adjacent cell.
- Now, drag the second-day cell to the right until you reach the last date of the month.
- Resize all cells with dates by selecting the columns and double-clicking on the line between them.
- Change the date formats for the top two dates to dd mmm in the Format Cells option.
This is how the attendance sheet should look like so far.
The next step is to insert weekdays in the attendance table. Follow the steps below.
- Click on the blank cell under the first date of the month and use the TEXT formula.
- Type =TEXT(firstday,”ddd”). Firstday in the formula here denotes the first day of the month in the sheet.
- Press ENTER.
You will see that the weekday on the first date of the month is displaying in that cell. Check the results by referring to a calendar.
- Drag the first cell till the last day of the month to get all weekdays.
- You can rotate the text in the Alignment section in the Home tab if you like.
The further steps are to automatically highlight Sundays in a month, restrict data entry on Sundays, and counting present and absent days.