Website Home Page |

Excel Assignments List

| Froyo Technologies Public Resources

| 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-02 | 2023-01-12 | In Progress |

| Task E          | 2023-01-06 | 2023-01-16 | Not Started |

| Task F          | 2023-01-10 | 2023-01-20 | Not Started |

| Task G          | 2023-01-12 | 2023-01-22 | Not Started |

| Task H          | 2023-01-15 | 2023-01-25 | Not Started |

| Task I          | 2023-01-18 | 2023-01-28 | Not Started |

| Task J          | 2023-01-22 | 2023-01-31 | Not Started |

| Task K          | 2023-01-25 | 2023-02-05 | Not Started |

| Task L          | 2023-01-28 | 2023-02-08 | Not Started |

| Task M          | 2023-01-30 | 2023-02-10 | 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 to Perform

1.      Calculate Duration of Each Task Using DATEDIF Function:

• In cell D2 (next to "End Date" column header), enter the formula =DATEDIF(B2, C2, "d") + 1 to calculate the duration of Task A.
• Copy the formula down to calculate durations for Task B (D3) and Task C (D4).

2.      Calculate Total Project Duration:

• In a new cell (for example, B6), enter the formula =MAX(C2:C4) - MIN(B2:B4) + 1 to calculate the total project duration.
• This formula calculates the difference between the latest end date and the earliest start date, plus one day.

3.      Use Data Validation for Task Statuses:

• Select the cell where you want to create the dropdown (for example, E2).
• Go to Data tab > Data Tools group > Data Validation.
• Choose List under Allow, and in the Source box, enter Complete,In Progress,Not Started to create a dropdown list for selecting task statuses.

4.      Apply Conditional Formatting for Overdue Tasks:

• Select the range of End Date column (C2).
• Go to Home tab > Conditional Formatting > New Rule.
• Choose Use a formula to determine which cells to format.
• Enter the formula =C2<TODAY() and set the desired formatting (e.g., red fill) for overdue tasks.

5.      Create a Gantt Chart to Visualize Project Timeline:

• Select the range including headers (A1).
• Go to Insert tab > Charts group > Choose Bar Chart > Select Stacked Bar or Bar with Start/End Dates.
• Adjust the chart options to represent tasks as bars with their start and end dates.