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

## 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 Make an Effort Estimation Sheet in Excel

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

**Download Practice Workbook**

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