If you are looking for ways **to create a project time estimation sheet in Excel**, this article will be helpful for you. Along with time estimation, we will also show the **cost estimation of projects** easily here. You can download the free template also in the following section. So, let’s get into the main article.

**Table of Contents**hide

## Download Practice Workbook

## 4 Steps to Create a Project Time Estimation Sheet in Excel

In the following paragraphs, we have illustrated the procedures of making a complete project time estimation sheet with the help of Excel. A project time estimation sheet is helpful for calculating the total time needed for the completion of a project.

For creating this article, we have used ** Microsoft Excel 365 version**. However, you can use any other version at your convenience.

__Step-01__: Creation of Basic Outlines to Form a Project Time Estimation Sheet in Excel

In this step, we will form some basic outlines required for the project estimation sheet.

- First, we need to have some basic details of the project summary. In the
**Data**sheet, we garnered the lists of tasks, employee names, their designation, and costs required per hour to complete each task.

- Now, create another sheet named
**Template**. - Make the following outline in this sheet with
**7**required columns;,*SL No*,*Tasks*,*Designation*,*Name*;*Planned Weeks*,*Estimated Weeks*,*Cost per hour*, and*Planned Cost*.*Estimated Cost*

- Now, move to the right side of this sheet to add
**10**more columns for**10**weeks to show how much percentage of work has been accomplished for different tasks. Here, we have shown the outline for**10**weeks only you can add more weeks if it is needed.

- Add serial numbers from
**1**to**19**in**SL No**.

**Read More: ****How to Calculate Residential Construction Cost Estimator in Excel**

__Step-02__: Adding Drop-down List and Giving Input Values for Weeks

In this step, we will insert drop-down lists for the **Designation **and **Name **columns. Furthermore, we will insert the percentages of the tasks completed from **Week 1 **to **Week 10**.

- First, select the cells of the
**Tasks**column, and then go to the**Data**tab >>**Data Validation**.

Afterward, the **Data Validation **dialog box will open up.

- Select the
**List**option under**Allow**. - Then, choose the list of
**Tasks**from the**Data**sheet and press**OK**. Then, the following formula will appear in the**Source**box.

`=Data!$B$5:$B$16`

Then, you will see the symbol for the drop-down list in the cells of the **Tasks **column.

- Click on the drop-down sign in cell
**C5**and choose any task from the list.

Here, we chose **Task 3**.

Similarly, complete the other cells of the **Tasks **column by choosing different tasks from the list.

- Now, select the cells of the
**Designation**column, and then go to the**Data**tab >>**Data Validation**.

Afterward, the **Data Validation **dialog box will open up.

- Select the
**List**option under**Allow**. - Then, choose the list of
**Designations**from the**Data**sheet and press**OK**. Then, the following formula will appear in the**Source**box.

`=Data!$C$5:$C$16`

Later, you will see the symbol for the drop-down list in the cells of the **Designation **column.

- Click on the drop-down sign in cell
**D5**and choose any**Designation**from the list.

Here, we chose the **Manager**.

In a similar manner, complete the other cells of the **Designation **column by choosing different designations from the list.

Now, give input the percentages of different tasks completed in different weeks in the following manner.

**Read More: ****How to Create Estimation Tool in Excel (with Easy Steps)**

__Step-03__: Applying Formulas

Here, we will use some formulas to complete the following indicated columns.

- Type the following formula in cell
**E5**to get the names corresponding to different designations.

`=INDEX(Data!$D$5:$D$16, MATCH(D5, Data!$C$5:$C$16,0))`

**Formula Breakdown**

**MATCH(D5, Data!$C$5:$C$16,0) →**becomes**MATCH(“Manager”, Data!$C$5:$C$16,0) →****The MATCH function**will return the row index number corresponding to the range**$C$5:$C$16**where*Manager***Output → 3**

**INDEX(Data!$D$5:$D$16, MATCH(D5, Data!$C$5:$C$16,0)) →**becomes**INDEX(Data!$D$5:$D$16, 3) →****The INDEX function**will return the name of the employee situated inof the range*Row 3***$D$5:$D$16**.**Output → Ursula**

- Press
**ENTER**and drag down the**Fill Handle**tool.

Later, you will get the name of the employees corresponding to their designations.

Now, we will extract the costs for different tasks from the sheet ** Data**.

- Write down the following formula in cell
**H5**and press**ENTER**.

`=INDEX(Data!$E$5:$E$16, MATCH(C5, Data!$B$5:$B$16,0))`

**Formula Breakdown**

**MATCH(C5, Data!$B$5:$B$16,0)****→**becomes**MATCH(“Task 3”, Data!$B$5:$B$16,0) → The MATCH function**will return the row index number corresponding to the range**$C$5:$C$16**where*Manager***Output → 3**

**INDEX(Data!$E$5:$E$16, MATCH(C5, Data!$B$5:$B$16,0)) →**becomes**INDEX(Data!$E$5:$E$16, 3) → The INDEX function**will return the cost for the corresponding task situated inof the range*Row 3***$E$5:$E$16**.**Output → $67.00**

- Drag down the
**Fill Handle**tool.

Finally, we will get the costs for different tasks.

- Write down your planned number of weeks for different tasks in the
**Planned Weeks**column.

It’s time to calculate the estimated or calculated times for completing each task on the basis of percentages of different tasks in different week columns.

- Put down the following formula in cell
**G5**and press**ENTER**.

`=IF(SUMIF($L5:$U5,">0")>0, SUMIF($L5:$U5,">0"),"")`

**Formula Breakdown**

**SUMIF($L5:$U5,”>0″) →****The SUMIF function**will sum up the percentages in the range**$L5:$U5**if their values are greater than**0**.**Output → 1.55**

**IF(SUMIF($L5:$U5,”>0″)>0, SUMIF($L5:$U5,”>0″),””) →**becomes**IF(1.55>0, 1.55,””) →****The IF function**will give**55**as output as the logical condition becomes**TRUE**, otherwise we will get a**blank**.**Output → 1.55**

- Drag down the
**Fill Handle**tool.

In this way, we will get the estimated weeks for different tasks.

Now, we will calculate the costs for our planned duration and the estimated duration of different tasks.

- Type the following formula in cell
**I5**and press**ENTER**.

`=IF(H5<>"",$F5*H5*5*8,"")`

**Formula Breakdown**

**$F5*H5*5*8 →**becomes**2*67*5*8**→ here we will multiply total weeks with cost per hour, and to convert the total weeks into their corresponding hours we are multiplying them by**5**(for weekdays) and**8**(daily working hours).**Output → 5360**

**IF(H5<>””,$F5*H5*5*8,””) →**becomes**IF(67<>””,5360,””) →**we will get**5360**when the logical condition will be**TRUE**otherwise a**blank**.**Output → 5360**

- Drag down the
**Fill Handle**tool.

Finally, we will get the costs for each task according to our planned data.

- Type the following formula in cell
**J5**and press**ENTER**.

`=IF(H5<>"",$G5*H5*5*8,"")`

**Formula Breakdown**

**$G5*H5*5*8****→**becomes**55*67*5*8**→ here we will multiply the total calculated weeks with cost per hour, and to convert the total weeks into their corresponding hours we are multiplying them by**5**(for weekdays) and**8**(daily working hours).**Output → 4154**

**IF(H5<>””,$G5*H5*5*8,””) →**becomes**IF(67<>””,4154,””) →**we will get**4154**when the logical condition will be**TRUE**otherwise a**blank**.**Output → 4154**

- Drag down the
**Fill Handle**tool.

Finally, we will get the calculated costs for each task.

**Read More: ****How to Make Cost Estimation Sheet in Excel**

__Step-04__: Calculating Total Values

In this step, we will calculate the total weeks and total costs for all of the projects.

- Type the following formula in cell
**G24**.

`=SUM(G5:G23)`

Here, **the SUM function** will return the total number of weeks.

- Press
**ENTER**.

Later, we will have the total number of weeks required to complete all of the tasks of this project.

- Enter the following formula in cell
**J24**.

`=SUM(J5:J23)`

**The SUM function **will give us the total costs required for completing the tasks.

**Read More: ****How to Perform Building Estimation and Costing in Excel Sheet**

## Conclusion

In this article, we have discussed different steps to **create a project time estimation sheet in Excel**. Hope these steps will help you a lot. If you have any further queries, then leave a comment below.