How to Create a Monthly Timesheet in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

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.

how to create a monthly timesheet in excel

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.

Create Basic Outline of the Monthly Timesheet in Excel

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

Changing the Name of the Sheet

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

Setting the Starting Date of the Month

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

making basic outline to create a monthly timesheet in excel


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

Generate Date and Day to Create a Monthly Timesheet in Excel

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.

Using Format Cells Dialog Box

  • Thus, it shows the date of 1st 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.

Utilizing Fill Handle Tool to Create Monthly Timesheet in Excel

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

Generating Date and Day


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.

Specify the Weekend

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

Applying New Formatting Rule

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

Using Format Cells Wizard

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

Specifying Weekend to Create Monthly Timesheet in Excel


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.

Enter Required Data


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.

Calculate Total Work Hours of Monthly Timesheet in Excel


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

  • Lastly, press the ENTER key.

Determine Regular and Overtime Hours

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

  • Subsequently, press ENTER.

Determining Regular and Overtime Hours to Create Monthly Timesheet in Excel


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.

Compute Total Payment

  • To begin with, go to cell J42.
  • Then, write down the formula below.
=SUM(J11:J41)
  • Finally, press the ENTER key.

Computing Overtime Hours of Monthly Timesheet in Excel

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

Calculating Total Payment of Monthly Timesheet in Excel


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.

Generate Timesheet for Another Month

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

Generating Timesheet for Another Month

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

Assuming Two Weekly Holidays

  • Also, clear all previous entries.

Clearing All Previous Entries

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

Generating another Monthly Timesheet in Excel


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

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