How to Make Time Attendance Sheet in Excel (2 Easy Ways)

If you work for a company or run an organization by yourself, then you might need to create a time attendance sheet in Excel for the company. In this article, we will show you how to make a time attendance sheet in Excel.

How to Make Time Attendance Sheet in Excel: 2 Easy Ways

In this section, you will find 2 suitable ways to make a time attendance sheet in Excel. Let’s check them now!

1. Make Generic Time Attendance Sheet

Let’s say, we have got a dataset of general information of different employees working for a company.

We want to make an attendance sheet for each of these employees denoting their respective entry time, exit time, working hours, etc. over a month.

To make a time attendance sheet with this dataset, proceed with the steps below.

Step 1: Assign Date & Day

• First of all, create a sheet table for one of the employees (i.e. Mike Almas) with multiple headings (i.e. Date, Day, Entry Time, Exit Time, Over Time, Break Time, etc.). Assign the date range and day for this sheet table. (I have used it just for 1 month. You may proceed with your acquainted time period).

• Now, select the row just below the header row> go to the View tab> click Freeze Panes> select Freeze Panes.

• Here, freezing panes will allow the header row not to move anymore with scrolling down the cursor.

Step 2: Assign Entry & Exit Time and Get Attendance

• Then, enter the Entry Time and Exit Time to the respective cell and apply the following formula to the cell where you want to show the working hour from entry to exit:

Here,

• E5= Entry Time
• D5= Exit Time

• After that, assign the Overtime and Break Time and apply the following formula for getting the grand total of the working hours:

Here,

• F5= Total (between Entry and Exit)
• G5= Overtime
• H5= Break Time

• Now, hit ENTER to get the output.

• However, enter the respective time every day and use the Fill Handle Tool to drag the formula down the cells to get the corresponding result.

• Here, Grand Total denotes the total working hours including overtime and break time.

Step 3: Holidays in Attendance Sheet

• Then, for the holidays (i.e., Friday), the value in the Total and the Grand Total cell will also exist. Removing them will remove the formula so avoid it. Rather select the cells for holidays> go to the Home tab> click Font Color> choose the font color just like the fill color of the cell (i.e. White).

• Hence, your cell won’t show the cell value. Actually, they exist, but they won’t show up because of the fill color.
• Now, create extra sheets for the other employees and repeat this same procedure for everyone.

2. Create Attendance Sheet with Presence and Absence

For our previous set of data, we will now create an attendance sheet with presence and absence. Here, we will use the CELL, FIND, and MID functions. For creating this sheet, follow the steps below.

Step 1: Create Month Name

• Firstly, select a cell and add the following formula:

• Then, hit ENTER and the cell will return the value. We have set the sheet name as January as we want to make a sheet for this month.

• Now, select a cell for the start date of the month and apply the DATEVALUE function:

Here,

• E2= Month of January

• After that, go to the Home tab> Number Format> select Long Date.

• Here, the cell will show the long date format.

• Hence, select another cell for the end date and apply the formula below:

Here,

• C4= Start Date

Step 2: Assign Respective Date & Day

• Now, choose a cell where you want to put the first date of the month and apply:

Here,

• C4= Start Date of the Month

• However, hit ENTER.

• Then, click CTRL+1 to open the Format Cells dialogue box. From the Custom icon, select d from Type> click OK.

• After that, the cell will show 1. (as date)

• Here, apply the IF function and assign the following formula to the next cell:

Here,

• D7= 1 (first date)

• Now, use the Fill Handle tool to drag the formula to the right cells to till it completes the day of the month.

• Here, the cells will show the dates.

• Then, apply the TEXT function and assign the following formula to the upper cell to get the day:

Here,

• D7= date
• ddd= first e letters of the day

• Now, drag the formula to the right end.

• After that, select all the days>click CTRL+1 and from the Format Cells dialogue box, select Alignment> type 90 in the degree section> click OK.

• Here, the days will be aligned to 90 degrees.

Step 3: Apply Conditional Formatting

• Now, select the cell for the first day of the first employee> go to the Home tab> Conditional Formatting> New Rule.

• Then, select the last rule from the list>write the formula> click Format:

Here,

• Fri= Holiday (as we want to format cells for holiday)

• After that, select a fill color> click OK.

• Now, click OK on the New Formatting Rule box.

• Hence, drag the formula to the last date and last person and the cells for holidays will be formatted as red like below.

Step 4: Assign Attendance as Present and Absent

Rather than using entry exit time assign the attendance status; Present or Absent.

• Now, select the blank cells (i.e. the working dates)> go to Data tab> Data Validation.

• Here, the Data Validation box will appear. Select List from Allow and assign P, A (denoting Present/ Absent) to Source box>click OK.

• After that, the drop-down button will appear for the selected cells. You can choose P or A from here to denote the Presence or Absence of an employee.

• Hence, assign the attendance for the sheet table.

• However, select the attendance> go to Home tab> Conditional Formatting> Highlight Cells Rules> Text that Contains.

• Here, choose a fill for P> click OK.

• Now, P will be formatted and you can easily distinguish between presence and absence.

In this way, you can make an attendance sheet with presence and absence in Excel.

Download Practice Workbook

Conclusion

In this article, you have learned how to make a time attendance sheet in Excel. Hope you can easily make a time attendance sheet in an Excel workbook from now on. If you have any better methods or questions or feedback regarding this article, please don’t forget to share them in the comment box. This will help me enrich my upcoming articles.

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