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-11 | 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-10 | 2023-01-20 | Complete     | John Doe    |

| Task B          | 2023-01-15 | 2023-01-25 | In Progress  | Jyne Smith  |

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

Task :

1. Calculate the duration of each task using DATEDIF function.
2. Use formulas to calculate the percentage completion of each task.
3. Create a stacked bar chart to visualize the status of tasks (Complete, In Progress, Not Started).
4. Use conditional formatting to highlight tasks that are overdue (End Date < Today).
5. Insert a PivotTable to summarize tasks by Assigned To and Status, and create a PivotChart to visualize it.

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

Steps to Perform:

1. Calculate the Duration of Each Task Using DATEDIF Function:
   • Use Excel's DATEDIF function to calculate the duration (in days) 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 is in column C.
2. Calculate Percentage Completion of Each Task Using Formulas:
   • Calculate the percentage completion of each task based on its status (Complete, In Progress, Not Started).
   • Example formula for percentage completion:

less

Copy code

=IF(D2 = "Complete", 100, IF(D2 = "In Progress", (TODAY() - B2) / (C2 - B2) * 100, 0))

Adjust the formula based on the specifics of your task statuses and conditions.

3. Create a Stacked Bar Chart to Visualize Task Status:
   • Select the data range including Task, Status, and possibly Duration columns.
   • Go to Insert > Bar Chart > Stacked Bar to create the chart.
   • Customize the chart to display Task on the x-axis, Duration (optional) on the y-axis, and stack bars by Status (Complete, In Progress, Not Started).
4. Use Conditional Formatting to Highlight Overdue Tasks:
   • Select the End Date column.
   • Go to Home > Conditional Formatting > New Rule > Format cells that are > Date Occurring > Before > TODAY().
   • Choose a formatting style to highlight tasks that have End Date earlier than today.
5. Insert a PivotTable to Summarize Tasks by Assigned To and Status, and Create a PivotChart:
   • Select the data range including Task, Assigned To, and Status.
   • Go to Insert > PivotTable > PivotTable to create a PivotTable.
   • Drag Assigned To to Rows and Status to Columns in the PivotTable field list.
   • Summarize values as needed (count of tasks, percentage of completion, etc.).
   • With the PivotTable selected, go to Insert > PivotChart > (choose appropriate chart type) to create the PivotChart.

Detailed Steps:

• Step 1: Calculate Task Duration
  • Enter =DATEDIF(B2, C2, "d") in a new column (e.g., D2) to calculate the duration in days between Start Date (B2) and End Date (C2).
• Step 2: Calculate Percentage Completion
  • Use an appropriate formula to calculate percentage completion based on task status.
  • Example: =IF(D2 = "Complete", 100, IF(D2 = "In Progress", (TODAY() - B2) / (C2 - B2) * 100, 0)).
• Step 3: Create Stacked Bar Chart
  • Select the data range including Task, Status, and possibly Duration.
  • Go to Insert > Bar Chart > Stacked Bar to create the chart.
  • Customize the chart with Task on the x-axis, Duration (optional) on the y-axis, and stack bars by Status.
• Step 4: Apply Conditional Formatting
  • Select the End Date column.
  • Go to Home > Conditional Formatting > New Rule > Format cells that are > Date Occurring > Before > TODAY().
  • Choose a formatting style to highlight overdue tasks.
• Step 5: Insert PivotTable and PivotChart
  • Select the data range including Task, Assigned To, and Status.
  • Go to Insert > PivotTable > PivotTable to create the PivotTable.
  • Configure the PivotTable fields (Assigned To in Rows, Status in Columns, and possibly count of tasks or other relevant data as Values).
  • With the PivotTable selected, go to Insert > PivotChart > (choose appropriate chart type) to create the PivotChart based on summarized task data.