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

We’ll use a sample dataset from a fictional HR’s information for an employee. Here’s the overview of the timesheet we’ll make.

Step 1- Create a Basic Outline of the Monthly Timesheet in Excel

Construct a heading in cell B2 and put it the Heading 2 cell style. We named it Monthly Timesheet.
In cell B4, write down the name of the company.
Place the Employee Name, Project Manager’s name, Contact No., Hourly Rate, and Overtime Rate. Create empty cells for Month, From, and To values.
In cells in the B10:J10 range, construct some headings like Date, Day, In time, etc.

Select cell D7 (Month).
Use the formula below in it:

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

Press the Enter key.
You’ll get the sheet name and the manually input year 2022.

Change the name of the sheet to Jan.

Select cell D8.
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.

Hit Enter.

Select cell I8 to show the ending date of that month.
Use the following formula in the cell and press Enter.

=EOMONTH(D8,0)

The EOMONTH function is used to determine the end of the month.

Step 2 – Generate the Dates and Corresponding Days

Select cell B11.
Paste in the following formula and press the Enter key.

=D8

The date is shown in the General Number format. We’ll need to convert it.

Press Ctrl + 1. This opens the Format Cells dialog box.
Go to the Number tab.
Select Custom from the Category section.
In the Type box, write d.
Click OK.

You’ll get the date of the first day of the month, displaying only the date part.
Select cell B12.
Paste the following formula into the cell.

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

Hit Enter.

Hover over the bottom-right corner of cell B12. The cursor will change to a plus (+) sign. It’s the Fill Handle tool.

Use the Fill Handle tool and drag it to cell B41 to show the other days of the month.

Select cell C11.
Put the following formula into the Formula Bar.

=TEXT(B11,"ddd")

ddd shows the first three letters of the weekday.

Press Enter.

Step 3 – Specify the Weekends

We’ll consider the Sunday as the weekend and will highlight those cells with Conditional Formatting.

Select cell D11.
Move to the Home tab.
Click on the Conditional Formatting drop-down on the Styles group.
Select New Rule from the available options.

This opens the New Formatting Rule wizard.
Choose Use a formula to determine which cells to format under the Select a Rule Type section.
Insert =$C11=”Sun” in the Format values where this formula is true: box.
Select the Format button.

The Format Cells wizard opens.
Move to the Fill tab.
Choose the Red color from the available options.
Cick OK.

This returns us to the New Formatting Rule dialog box.
Click OK again.

Use the Fill Handle tool to expand the formatting across the D11:J41 range.

Step 4 – Enter Data

Enter the necessary data like In Time, Lunch Start, Lunch End, and Out Time in the sheet. We’ve put some sample data into the sheet.

Step 5 – Calculate the Total Work Hours

Go to cell H11 (or wherever you’ve put the value for total work hours for the day).
Use the following formula.

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

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. The multiplication by 24 converts the time to hours.

Hit Enter and AutoFill through the column.

Step 5 – Determine Regular and Overtime Hours

We’ll assume anything over 8 working hours (not including the lunch break) is paid overtime.

Select cell I11.
Use the following formula.

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

The H11 cell refers to the Total Work Hours.

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

Hit Enter.

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

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

Hit Enter.
AutoFill the formulas throughout the columns.

Step 7 – Compute the Total Payment

Select cell I42.
Insert the following formula.

=SUM(I11:I41)

Hit Enter.

Go to cell J42.
Use the formula below.

=SUM(J11:J41)

Hit Enter.

Select cell I43 and enter 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.