How to Create a Project Time Estimation Sheet in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

Creation of Basic Outlines to Form a Project Time Estimation Sheet in Excel

  • 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, Planned Cost, and 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.

Creation of Basic Outlines to Form a Project Time Estimation Sheet in Excel

  • Add serial numbers from 1 to 19 in SL No.

serial numbers

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.

Adding Drop-down List to form project time estimation sheet in Excel

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

data validation

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.

Adding Drop-down List to form project time estimation sheet in Excel

  • 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

data validation

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.

Adding Drop-down List to form project time estimation sheet in Excel

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

Giving input values to weeks to form project time estimation sheet in Excel

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.

Adding Formulas to form project time estimation sheet in Excel

  • 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 in Row 3 of the range $D$5:$D$16.
      • Output → Ursula

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

Adding Formulas to form project time estimation sheet in Excel

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 in Row 3 of the range $E$5:$E$16.
      • Output → $67.00

  • Drag down the Fill Handle tool.

Finally, we will get the costs for different tasks.

Adding Formulas to form project time estimation sheet in Excel

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

write down week numbers

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.

formula

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

Adding Formulas to form project time estimation sheet in Excel

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.

planned cost

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

Adding Formulas to form project time estimation sheet in Excel

  • Type the following formula in cell J5 and press ENTER.
=IF(H5<>"",$G5*H5*5*8,"")

Formula Breakdown

  • $G5*H5*5*8becomes
    • 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.

estimated cost

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

Adding Formulas to form project time estimation sheet in Excel

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.

calculating total values to form project time estimation sheet in Excel

  • Type the following formula in cell G24.
=SUM(G5:G23)

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

SUM function

  • Press ENTER.

calculating total values to form project time estimation sheet in Excel

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.

total cost


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.


Related Articles


<< Go Back to Excel Project Estimate Templates | Excel Project Management Templates | Excel Templates

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.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo