Assignment -14 | Project Task Timeline and Status Overview

Excepted timing : 10 minutes

| Task            | Start Date | End Date   | Status   |
|-----------------|------------|------------|----------|
| Task A          | 2023-01-01 | 2023-01-10 | Complete |
| Task B          | 2023-01-05 | 2023-01-15 | In Progress |
| Task C          | 2023-01-08 | 2023-01-18 | Not Started |
| Task D          | 2023-01-11 | 2023-01-15 | Complete |
| Task E          | 2023-01-15 | 2023-01-19 | In Progress |
| Task F          | 2023-01-20 | 2023-01-25 | Not Started |
| Task G          | 2023-01-11 | 2023-01-21 | Complete |
| Task H          | 2023-01-15 | 2023-01-25 | In Progress |
| Task I          | 2023-01-18 | 2023-01-21 | Not Started |

 
Task :
  1. Calculate the duration of each task using DATEDIF function.
  2. Calculate the total project duration using formulas.
  3. Use data validation to create a drop-down list for selecting task statuses (Complete, In Progress, Not Started).
  4. Apply conditional formatting to highlight tasks that are overdue (End Date < Today).
  5. Create a Gantt chart to visualize the project timeline with tasks and their durations.

To perform the tasks outlined in Assignment  using Excel, follow these step-by-step instructions:

 

Step 1: Calculate Task Durations using DATEDIF Function

  1. Enter Data: Enter your data into Excel with the following columns:
    • A1: Task
    • B1: Start Date
    • C1: End Date
    • D1: Status

Enter your data starting from A2 to D4 (based on your provided example).

  1. Calculate Duration:
    • In cell E1, enter Duration.
    • In cell E2, enter the formula =DATEDIF(B2, C2, "d") + 1.
    • Copy the formula from E2 down to cells E3

to calculate durations for all tasks.

Step 2: Calculate Total Project Duration

  1. Formula:
    • In a suitable cell (e.g., A6), enter Total Project Duration.
    • In cell B6, enter the formula =MAX(C2:C4) - MIN(B2:B4) + 1.

Step 3: Use Data Validation for Task Status

  1. Data Validation:
    • Select a cell where you want the drop-down list (e.g., D6).
    • Go to the Data tab > Data Tools > Data Validation.
    • Choose List under Allow.
    • In Source, enter Complete, In Progress, Not Started.
    • Click OK.

Step 4: Apply Conditional Formatting for Overdue Tasks

  1. Conditional Formatting:
    • Select the range of dates in column C (End Date column).
    • Go to the Home tab > Conditional Formatting > New Rule.
    • Choose Use a formula to determine which cells to format.
    • Enter the formula =C2 < TODAY() (assuming your data starts from row 2).
    • Click on Format to choose how you want to format overdue tasks (e.g., with a red fill).

Step 5: Create a Gantt Chart to Visualize Project Timeline

  1. Select Data:
    • Select cells A1(including headers and data).
  2. Insert Chart:
    • Go to the Insert tab > Charts > Bar > Stacked Bar (or Stacked Bar 100%).
  3. Format Chart:
    • Adjust the chart to represent the tasks along the timeline:
      • Set Task as the category axis labels.
      • Set Start Date and Duration (calculated from End Date - Start Date) as the data series.
      • Optionally, add Status as a legend.