Attendance Sheet with Salary in Excel Format (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

This article shows you how to make an attendance sheet with salary in Excel format. One of the most important responsibilities of the HR department in any company is managing employee attendance and salary. This work will be made simple with a solid attendance sheet. Here we’ll take you through 6 easy and convenient steps to create an attendance sheet with salary in Excel format.


Make Attendance Sheet with Salary Format in Excel: 6 Steps

Here, we have information about some employees of an organization. In the dataset, columns C, D, and E contain the consecutive Employee ID, Employee Name, and Designation.

attendance sheet with salary in excel format dataset

Now, we wanna create an automatic attendance sheet with salary format in Excel. Before proceeding any further, save the Excel file first.

attendance sheet with salary in excel format saving file


Step 01: Put the Name of the Month

Here, we want to create monthly attendance and salary sheet format in Excel. To do this, we have to insert the name of the month on the top portion of the format. Follow our steps below.

  • At first, select cell F2. Write down the formula below and press ENTER.
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)&" "&2022

Formula Breakdown: 

This formula returns the sheet name in the selected cell.

  • CELL(“filename”,A1): The CELL function gets the complete name of the worksheet
  • FIND(“]”,CELL(“filename”,A1)) +1: The FIND function will give you the position of ] and we’ve added +1 because we require the position of the first character in the name of the sheet.
  • 255: Excel’s maximum word count for the sheet name.
  • MID: The MID function uses the text’s position from start to end to extract a specific substring.

attendance sheet with salary in excel format putting name of the month

At this point, we can see the name of our Sheet on this cell with 2022.

Note: While typing this formula, make sure to enter any cell reference of this sheet. Otherwise, the formula won,t work properly. For example, here we’ve entered the reference of cell A1.

  • After that, change the name of the sheet to May. As we wanna make the attendance sheet of month May’22. We can easily see that the month name is automatically input into cell F2 after changing the sheet name.

attendance sheet with salary in excel format putting name of the month

  • After that, we’ve to set the starting date for this month. Select cell C4 and input the formula below.
=DATEVALUE("1"&F2)

Here, F2 represents the month of May 2022.

A date that is stored as text can be changed into a serial number that Excel can identify as a date using the DATEVALUE function.

attendance sheet with salary in excel format putting name of the month

Our value comes as a serial number. But we wanna show it as a date. So, change the format of this cell to Date.

  • Then, select cell C4. Go to the Home tab and select Number group > More Number Formats from the drop-down menu.

attendance sheet with salary in excel format putting name of the month

  • Format Cells dialog box opens. Now, select Date from Category. Then, choose the following Type as the image below and click on OK.

attendance sheet with salary in excel format putting name of the month

Note: Also, you may press CTRL + 1 to open the Format Cells dialog box.

  • After that, select cell G4 to show the ending date of that month. Write down the formula in the cell and press ENTER.
=EOMONTH(C4,0)

Here, C4 represents the starting date of the month.

EOMONTH function is used to determine the end of the month. This result will also show in Number format. So, format cell G4 with Format Painter just as cell C4.

attendance sheet with salary in excel format putting name of the month


Step 02: Create Individual Date and Day

To provide attendance, we must create different cells of different dates and days.

Follow the steps below.

  • Select cell F7. Type the formula below and press ENTER.
=C4

attendance sheet with salary in excel format creating individual day

  • Again, press CTRL + 1 to open the Format Cells wizard. Select Custom and type d in the Type box. Click on OK.

attendance sheet with salary in excel format creating individual day

  • At this point, the date looks like the image below.

attendance sheet with salary in excel format creating individual day

  • To input the second date, select cell G7. Write the formula below and press ENTER.
=IF(F7<$G$4,F7+1,"")

Here, F7 and G4 represent the first date of the month and the last date of the month successively.

attendance sheet with salary in excel format creating individual day

Formula Breakdown:

We’ve applied a logical test using the IF function here. If the date in cell F7 is less than the month’s end date, then the formula returns the next date of the date in cell F7. Otherwise, it will show nothing.

  • Use the Fill Handle tool and drag it to cell AJ7 to exhibit the rest days of the month.

attendance sheet with salary in excel format creating individual day

  • After that, select the columns in the F: AJ range. Go to the Home tab. Select Cells > Format > Column Width.

attendance sheet with salary in excel format creating individual day

  • Type 4 in the Column Width box and click on OK.

attendance sheet with salary in excel format creating individual day

  • Consequently, we have our column in the perfect width because we will just put P or A into these cells.

attendance sheet with salary in excel format creating individual day

  • Later, select cell F6 and type the formula below. Then, press ENTER.
=TEXT(F7,"ddd")

attendance sheet with salary in excel format creating individual day

  • Indicate the other days till the date 31 using the Fill Handle Tool. From the Home tab, click on the small arrow in Alignment group > write down 90 in the Degrees box and click OK.

attendance sheet with salary in excel format creating individual day

  • However, the name of the days rotates vertically by our previous action.

attendance sheet with salary in excel format creating individual day

  • After that, fill the cells with your preferred colors and apply All Borders to the cells.

attendance sheet with salary in excel format creating individual day


Step 03: Format Weekly Holidays in Attendance Sheet with Salary Format in Excel

As Sunday is the weekly holiday, we don’t have to input anything into those cells. We will highlight those cells with Conditional Formatting.

  • At first, select cell F8. Go to the Home tab and select Conditional Formatting > New Rule.

attendance sheet with salary in excel format formatting weekly holidays

  • At this moment, the New Formatting Rule dialog box will open. Select the options as in the image below. Write the formula in the formula box and click on Format.
=F$6=”sun”

attendance sheet with salary in excel format formatting weekly holidays

  • Then, the Format Cells dialog box opens. Select the Fill tab > Choose your preferred color and click on OK.

attendance sheet with salary in excel format formatting weekly holidays

  • It returns to the New Formatting Rule dialog box. After that, click on OK.

attendance sheet with salary in excel format formatting weekly holidays

  • Use the Fill Handle tool to expand these formatting in cells in the F8:AJ17 range. The cells under the columns of the Sun are highlighted with red color.

attendance sheet with salary in excel format formatting weekly holidays


Step 04: Input Attendance in Cells as P and A

The main objective of this article is to create an attendance sheet format. For this, we have to input present/absent in cells in the F8:AJ17 range.

  • Firstly, select cells in the F8:AJ17 range. Go to the Data tab and select Data Tools > Data Validation.

attendance sheet with salary in excel format inputting attendance

  • At this point, the Data Validation dialog box opens. Go to Settings. Select List from the drop-down list in Allow box. Write down P, and A in the Source box. Now, click on OK.

Here, P means Present, and A means Absent.

attendance sheet with salary in excel format inputting attendance

  • Secondly, click on cell G8. You can see a drop-down list containing just P and A. That means we could just input these 2 texts.

attendance sheet with salary in excel format inputting attendance

  • Thirdly, place the attendance of all individuals in their respective cells. A portion of attendance is like below.

attendance sheet with salary in excel format inputting attendance

  • Fourthly, select the cells in the F8:AJ17 range. Go to the Home tab. Then, select Conditional Formatting > Highlight Cells Rules > Text that Contains.

attendance sheet with salary in excel format inputting attendance

  • Then, in the Text That Contains dialog box, write down P in the Format cells that contain the text box. Choose the formatting as in the image below. Lastly, click OK.

inputting attendance as P and A

  • Cells containing P reflect the defined highlighting.

inputting attendance as P and A

  • Similarly, format the cells containing A with a different color and formatting.

inputting attendance as P and A


Step 05: Count Total Payable Days

Before calculating salary, it’s very crucial to count the total days, total working days, and total present and absent days. Because based on those, the salary gets disbursed.

  • At first, select cell AK8 to determine the present days. Type the formula below and press ENTER.
=COUNTIF(F8:AJ8,"P")

Here, F8:AJ8 represent the cell range to input attendance.

Here, we used the COUNTIF function to count P in the above range.

Counting total payable days

  • Secondly, select cell AL8. Type the formula below and press ENTER.
=COUNTIF(F8:AJ8,"A")

Counting total payable days

  • To calculate the total holidays in the month, select cell AM8. Put the formula below and press ENTER.
=COUNTIF($F$6:$AJ$6,"sun")

Counting total payable days

  • Then, we calculated the Total Days of the Month also. Select cell AM8 and write the formula below. After that, press the ENTER key.

Counting total payable days

  • To calculate Total Payable Days, firstly, select cell AO8. Write down the formula below and press ENTER.
=IF(AK8=0,0,AK8+AM8)

In this place, AK8 and AM8 cells serve as Present Days and Holidays respectively.

Formula Breakdown:

If the number of present days is zero, then the formula returns zero also. That means if an employee becomes absent through the whole month, then he’ll not get paid for the holidays also. Otherwise, he’ll be paid for his present days and weekly holidays.

Counting total payable days


Step 06: Calculate Salary in the Attendance Sheet with Salary Format in Excel

For calculating salary, we have their Total Salary amount.

Calculating Salary

  • However, here a 5% provident fund is deducted from the Total Salary. To do this, select cell AQ8. Write down the formula below and press ENTER.
=AP8*5%

Calculating Salary

  • Finally, to calculate the Salary in Hand, select cell AR8. Type the formula below and press ENTER.
=IF(AO8>0,((AP8/AN8)*AO8)-AQ8,0)

Here, AN8, AO8, AP8, and AQ8 represent Total Days of Month, Total Payable Days, Total Salary, and PF consecutively.

Calculating Salary

  • Then, format the cells with effective colors to make the data more visible.

Calculating Salary

  • At last, use the Freeze Panes tool to freeze the columns in the A:E range and freeze the rows in the 1:7 range. For this, at first, select cell F8 and go to the View tab. Select Window group > Freeze Panes drop-down menu > Freeze Panes tool.

Using Freeze Panes Tool

  • Now, the cells on left and above F8 become frozen. It is very helpful as our sheet is expanded horizontally.

Using Freeze Panes Tool


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


Conclusion

Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.


<< Go Back to Salary | Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo