As such, there is no specific formula of creating all the elements of timesheet at once. It’s a step by step process of putting few different formulas and keys in Excel. And FInal Result can be seen below:

*Figure 1 – Final Result*

**Step 1**

* Figure 2 – Setting up data*

This is an example of an individual that works in a company and gets paid on hourly basis.

- Categorize the cells in the first row according to your requirements.
- Click on column A, select “format cells”, click on “date” and choose the format of date.
- Write date in A2 and drag it to A11. The date for the next 9 days will automatically appear.
- Time in and Time Out is filled on daily basis by the individual.

**Step 2**

* Figure 3 – Calculate “Total number of working hours”
*

In this step, we will find out the “Total Number of working Hours” by using a formula.

**Formula: =(time out-time in)*24 **

**Or**

** =(C2-B2)* 24 in this case**

- Calculate the total working hours by entering the formula

in cell D2.**=(C2-B2)*24** - Select cell D2 and copy the formula, then select column D and choose “formulas” from “pasting options”.
- Excel will automatically calculate the working hours for the rest of the days.

**Step 3**

* Figure 4 – Calculate “Gross Pay”*

In step no.3, we are going to find out “Gross Pay” with the help of another simple formula.

**Formula: =Hourly rate*Working hours **

**Or**

` =H6*D2`

in this case

- Write formula

in the cell E2 to calculate gross pay.**=H6*D2** - Lock the formula by clicking “F4” and press “Enter”.
- Copy the formula from cell
**E2**and then click column**E**and select pasting formulas.

**Step 4**

* Figure 5 – Final result*

The final step of the worksheet is to calculate the payment of the worker for 10 days.

**Formula: =SUM(Gross Pay Column) **

**Or**

** =SUM(E:E)**

**Select cell H10.**- Write
`=SUM(E:E)`

and press Enter. - Instead of writing E:E, you can also simply click on column E to get the same result.

**Notes:**

**We can continue with the same timesheet for the 11th working day or for the whole month.**- Excel will automatically calculate the gross pay and total pay on entering data every day.
- While entering formulas, if we counter with #VALUE! Or #####, we can click the text and “delete” and re-write the text of our choice.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment