Are you badly in need of creating a monthly timesheet? If the answer is yes, then you’ve come to the right place. In Excel, managing and tracking work times are so simple. In this article, we will take you through 8 easy and quick steps on how to create a monthly timesheet in Excel.

## What Is a Monthly Timesheet?

An organization can monitor the hours a particular employee performs over a month using a data table. Generally, it’s the monthly timesheet. Timesheets have been maintained using a variety of ways, including paper, excel sheets, and online-based monitoring tools. Nowadays, we maintain the timesheets in digital formats omitting the paper-based ones.

## How to Create a Monthly Timesheet in Excel: 8 Easy Steps

Generally speaking, the HR department of an organization manages timesheets that involve recording and evaluating employee timekeeping. Additionally, it may entail performing various tasks, such as calculating employee payroll. So, without further delay, let’s see the process in detail.

Here, we have used *Microsoft Excel 365* version, you may use any other version according to your convenience.

__Step 01__: Create Basic Outline of Monthly Timesheet in Excel

Here, we’re showing the full process in different steps so that it is easy to understand. First of all, we need to create a basic outline of the monthly worksheet. So, just follow along.

- At the very beginning, construct a tempting heading in cell
**B2**. And, put it the**Heading 2**cell style. In this case, we named it Monthly Timesheet. - In cell
**B4**, write down the name of the company. Here, we assumed it FirstCall Steel. - Also, place the Employee Name, Project Manager’s name, Contact No., Hourly Rate, and Overtime Rate.
- Then, in cells in the
**B10:J10**range, construct some headings like Date, Day, In time, etc.

- Now, select cell
**D7**. - Then, write down the formula below.

`=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.

- After inserting the formula, press the
**ENTER**key. - 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*.

- Later, change the name of the sheet to Jan. As we wanna make the monthly timesheet of month Jan’22. We can easily see that the month name is automatically input into cell
**D7**after changing the sheet name.

- After that, we’ve to set the starting date for this month. So, select cell
**D8**. - Then, input the following formula.

`=DATEVALUE("1"&D7)`

Here, **D7** represents the month of *Jan 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**.

- As always, tap
**ENTER**.

- After that, select cell
**I8**to show the ending date of that month. - Lastly, write down the formula in the cell and press
**ENTER**.

`=EOMONTH(D8,0)`

Here, **D8** represents the starting date of the month. **The** **EOMONTH function** is used to determine the end of the month.

__Step 02__: Generate Date and Corresponding Day

To build a timesheet, we must create different cells of different dates and days. Follow the steps below.

- At the very beginning, select cell
**B11**. - Then, paste the following formula and press the
**ENTER**key.

`=D8`

Here, the date is shown in **Number** format. So, we’ve to convert it into a date.

- At first, press
**CTRL + 1**. - Immediately, it opens the
**Format Cells**dialog box. - Here, go to the
**Number**tab. - Then, select
**Custom**from the**Category**section. - In the
**Type**box, write down**d**. - Correspondingly, click
**OK**.

- Thus, it shows the date of
*1*^{st}*Jan*. - Again, select cell
**B12**. - After that, paste the following formula into the cell.

`=IF(B11<$I$8,B11+1,"")`

Here, **B11** and **I8** represent the first date of the month and the last date of the month successively. We’ve applied a logical test using **the IF function** here. If the date in cell **B11** is less than the month’s end date, then the formula returns the next date of the date in cell **B11**. Otherwise, it will show nothing.

- As usual, hit
**ENTER**.

- At this moment, bring the cursor to the bottom right corner of cell
**B12**. Thus, it’ll look like a plus**(+)**sign. It’s the**Fill Handle**tool.

- Now, use the
**Fill Handle**tool and drag it to cell**B41**to exhibit the rest days of the month.

- After that, we’ve to input the Day of the corresponding Date.
- For this, select cell
**C11**. - Then, put the following formula into the
**Formula Bar**.

`=TEXT(B11,"ddd")`

Here, we used **the TEXT function** to convert the date into a text string. Also, we give the format as **ddd** to show the first three letters of the name of a day.

- Subsequently, press
**ENTER**.

__Step 03__: Specify the Weekend of Monthly Timesheet 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**. Let us go through the steps.

- Firstly, select cell
**D11**. - Secondly, move to the
**Home**tab. - Thirdly, click on the
**Conditional Formatting**drop-down on the**Styles**group. - Fourthly, select
**New Rule**from the available options.

- Suddenly, it opens the
**New Formatting Rule**wizard. - Here, choose to
**Use a formula to determine which cells to format**under the**Select a Rule Type**section. - Then, we’ve to make some edits in the
**Edit the Rule Description**section. - Later, write down
**=$C11=”Sun”**in the**Format values where this formula is true:**box. - After that, select the
**Format…**button.

- As a result, the
**Format Cells**wizard opens. - Then, move to the
**Fill**tab. - Later, choose the
**Red**color from the available options. - Next, click
**OK**.

- Again, it returns us to the
**New Formatting Rule**dialog box. - Here, click
**OK**again.

- Currently, use the
**Fill Handle**tool to expand these formatting in cells in the**D11:J41**range. The cells in the rows of the Sun are highlighted with red color.

__Step 04__: Enter Required Data to Create Monthly Timesheet in Excel

At this time, we should enter the necessary data like In Time, Lunch Start, Lunch End, and Out Time in the sheet. Here, we’ve put some sample data into the sheet.

__Step 05__: Calculate Total Work Hours

In this step, we’ll compute the Total Work Hours for each day of the month. So, let’s see it in action.

- To begin with, go to cell
**H11**. - Following this, write down the following formula.

`=((G11-D11)-(F11-E11))*24`

Here, the **D11** and **G11** cells represent the *In Time* and *Out Time* while the **E11** and **F11** cells refer to the *Lunch Start* and *Lunch End* time respectively. Moreover, the multiplication by 24 converts the time to hours.

- Afterward, press the
**ENTER**button.

__Step 06__: Determine Regular and Overtime Hours

In the sixth step, we’ll calculate the Regular Time and the Overtime hours. It’s simple & easy, just follow along.

- Initially, select cell
**I11**. - After that, paste the following formula.

`=IF(H11>=8,8,H11)`

In this expression, the **H11** cell refers to the *Total Work Hour*.

**Formula Breakdown**

**IF(H11>=8,8,H11)**→ checks whether a condition is met and returns one value if**TRUE**and another value if**FALSE**. Here,**H11>=8**is the**logical_test**argument that compares the value in the**H11**cell with**8**. If this value is greater than or equal to**8**then the function returns**8 (value_if_true argument)**otherwise it returns the value of the cell**H11 (value_if_false argument)**.**Output**→**9**

- Lastly, press the
**ENTER**key.

*Note:** Here, we’ve considered 8 hrs as the regular working time*.

- Subsequently, we’ll calculate the
*Overtime*. - For this, select cell
**J11**and put the following formula into the cell.

`=IF(H11<=8,0,H11-I11)`

Here, the **H11** and **I11** cells point to the *Total Work Hour* and *Regular Office Time* respectively.

**Formula Breakdown**

**IF(H11<=8,0,H11-I11)**→ checks whether a condition is met and returns one value if**TRUE**and another value if**FALSE**. Here,**H11<=8**is the**logical_test**argument that compares the value in the**H11**cell with 8. If this value is less than or equal to**8**then the function returns**0 (value_if_true argument)**otherwise it returns the value**H11-I11 (value_if_false argument)**.**Output**→**1**

- Subsequently, press
**ENTER**.

__Step 07__: Compute Total Payment

In this step, we’ll compute the Total Payment using **The SUM function** of Excel. So, let’s begin.

- Primarily, select cell
**I42**. - Secondarily, insert the following formula.

`=SUM(I11:I41)`

- Lastly, press
**ENTER**.

- To begin with, go to cell
**J42**. - Then, write down the formula below.

`=SUM(J11:J41)`

- Finally, press the
**ENTER**key.

- At last, select cell
**I43**and paste the following formula.

`=I42*I6+J42*I7`

In the above formula, **I42**, and **I6** cells indicate the Total Hours (Regular Office Time) and the Hourly Rate of $25. Additionally, the **J42** and **I7** cells refer to the Total Hours (Overtime) and the Overtime Rate of $40.

- Eventually, hit
**ENTER**.

__Step 08__: Generate Timesheet for Another Month

Now, we’ll create the same type of sheet for the next month.

- Initially, right-click on the
**Sheet Name**of the Jan worksheet. - Then, select
**Move or Copy**.

- Secondly, select the options as shown in the image below and click on
**OK**.

- Now, look at cell
**D7**. It gets changed with the sheet name.

- After that, write down Feb in our 2nd Sheet Name. You can see that the worksheet is totally changed according to February month. The weekly days were set up as consecutive dates. Also, you will notice the start and end date of the month get changed in cells
**D8**and**I8**automatically.

- In this sheet, we’ve assumed 2 weekly holidays: Sat and Sun. You can change this according to your preferences.

- Also, clear all previous entries.

- At the end of the sheet, you can see that the number of days automatically changed according to the month.

**Download Practice Workbook**

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

## Conclusion

This article provides easy and brief solutions to create a monthly timesheet in Excel. Don’t forget to download the Practice Workbook. 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 Timesheet | Formula List | Learn Excel**