Are you badly in need to create 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.
You may download the following Excel workbook for better understanding and practice yourself.
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.
8 Steps to Create a Monthly Timesheet in Excel
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 gets 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.
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.
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.
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 ENTER key.
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 1st Jan.
- Again, select cell B12.
- After that, paste the following formula into the cell.
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.
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.
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.
In this expression, the H11 cell refers to the Total Work Hour.
- 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.
Here, the H11 and I11 cells point to the Total Work Hour and Regular Office Time respectively.
- 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.
- Lastly, press ENTER.
- To begin with, go to cell J42.
- Then, write down the formula below.
- Finally, press the ENTER key.
- At last, select cell I43 and paste the following formula.
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.
This article provides easy and brief solutions to create a monthly timesheet in excel. Don’t forget to download the Template File. 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. Please visit our website Exceldemy to explore more.