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.


Download Practice Workbook


2 Easy Ways to Make Time Attendance Sheet in Excel

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.

dataset for Makingf Time Attendance Sheet in Excel

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

Make Time Attendance Sheet in Excel

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

Make Time Attendance Sheet in Excel

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

Make Time Attendance Sheet in Excel


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:

=E5-D5

Here,

  • E5= Entry Time
  • D5= Exit Time

Make Time Attendance Sheet in Excel

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

=F5+G5+H5

Here,

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

Make Time Attendance Sheet in Excel

  • Now, hit ENTER to get the output.

Make Time Attendance Sheet in Excel

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

Make Time Attendance Sheet in Excel

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

Make Time Attendance Sheet in Excel

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


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

Make Time Attendance Sheet in Excel

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

Make Time Attendance Sheet in Excel

Read More: Attendance Sheet with Salary in Excel Format (with Easy Steps)


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:

=MID(CELL("filename", A1), FIND("]", CELL("filename", A1))+1,255)&" "&2022

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

=DATEVALUE(“1”&E2)

Here,

  • E2= Month of January

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

Make Time Attendance Sheet in Excel

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

Make Time Attendance Sheet in Excel

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

=EOMONTH(C4,0)

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:

=C4

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:

=IF(D7<$F$4,D7+1,"")

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:

=TEXT(D7,”ddd”)

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.

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


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:

=J$6=”Fri”

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.

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


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. For more queries, kindly visit our website ExcelDemy. Have a great day!


Related Articles

Rafi

Rafi

Hey there! I am Md. Rafiul Hasan. Currently I am working as an Excel & VBA content developer. I like new ideas and want to explore the field of innovation. Excel has saved our worktime and made it easy for us to quick calculations. I am trying to make it easier for you to overcome the obstacles you face while working on Excel. Stay connected!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo