Website Home Page |

Excel Assignments List

| Froyo Technologies Public Resources

Sample Data

| Task      | Start Date | End Date   | Status       | Assigned To |

|-----------|------------|------------|--------------|-------------|

| Task A    | 2023-01-01 | 2023-01-10 | Complete     | John Doe    |

| Task B    | 2023-01-05 | 2023-01-15 | In Progress  | Jane Smith  |

| Task C    | 2023-01-08 | 2023-01-18 | Not Started  | David Brown |

| Task A    | 2023-01-01 | 2023-01-10 | Complete     | John Doe    |

| Task B    | 2023-01-05 | 2023-01-15 | In Progress  | Jane Smith  |

| Task C    | 2023-01-08 | 2023-01-18 | Not Started  | David Brown |

| Task B    | 2023-01-01 | 2023-01-10 | Complete     | John Doe    |

| Task C    | 2023-01-05 | 2023-01-15 | In Progress  | Jane Smith  |

| Task D    | 2023-01-08 | 2023-01-18 | Not Started  | David Brown |

 

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:

 

Steps to Perform:

1. Calculate Duration of Each Task Using DATEDIF Function:
   • Use the DATEDIF function in Excel to calculate the duration of each task based on Start Date and End Date.
   • Example: =DATEDIF(B2, C2, "d") assuming Start Date is in column B and End Date in column C.
2. Calculate Total Project Duration Using Formulas:
   • Calculate the total project duration by determining the earliest Start Date and the latest End Date across all tasks.
   • Example: =MAX(C2:C4) - MIN(B2:B4) assuming Start Date is in column B and End Date in column C for tasks in rows 2 to 4.
3. Use Data Validation to Create a Drop-Down List for Task Statuses:
   • Select a cell where you want the drop-down list (e.g., D2 for Task A).
   • Go to Data > Data Validation > Allow: List.
   • Enter the statuses (Complete, In Progress, Not Started) separated by commas in the Source box.
4. Apply Conditional Formatting to Highlight Overdue Tasks:
   • Select the End Date column (e.g., column C).
   • Go to Home > Conditional Formatting > New Rule > Format cells that are > Cell Value > less than > =TODAY().
   • Choose a formatting style to highlight overdue tasks.
5. Create a Gantt Chart to Visualize Project Timeline:
   • Organize data with Task, Start Date, End Date, and Duration (calculated in Step 1) columns.
   • Select the data range including these columns.
   • Go to Insert > Bar Chart > Stacked Bar (or Bar) to create a basic Gantt chart.
   • Customize the chart by setting Start Date as the beginning of the bar, Duration as the length, and Task as the task names.

Detailed Steps:

• Step 1: Calculate Duration of Each Task
  • Enter =DATEDIF(B2, C2, "d") in a new column (e.g., D2) to calculate the duration between Start Date (B2) and End Date (C2). Copy the formula for other tasks.
• Step 2: Calculate Total Project Duration
  • Enter =MAX(C2:C4) - MIN(B2:B4) assuming Start Date is in column B and End Date in column C for tasks in rows 2 to 4.
• Step 3: Use Data Validation
  • Select a cell (e.g., D2) and go to Data > Data Validation > Allow: List. Enter "Complete, In Progress, Not Started" as the source.
• Step 4: Apply Conditional Formatting
  • Select the End Date column (e.g., C2).
  • Go to Home > Conditional Formatting > New Rule > Format cells that are > Cell Value > less than > =TODAY().
  • Choose a formatting style to highlight overdue tasks.
• Step 5: Create Gantt Chart
  • Select the data range including Task, Start Date, and Duration columns.
  • Go to Insert > Bar Chart > Stacked Bar (or Bar).
  • Customize the chart by setting Start Date as the starting point of bars, Duration as the length, and Task as the task names. Adjust axis labels and chart title as needed.