Excel Gantt Diagramm erstellen 2.0 - bedingte Formatierung [Kalenderwoche, Meilenstein, Vorlage]

The video starts by outlining the column headers for the Gantt chart, including a running number, separate columns for 'Phase', 'Task', and 'Milestone', start and end dates, and two 'Progress' columns (planned and actual). Project data is then populated, ensuring phases, tasks, and milestones are correctly assigned.

Next, date ranges are added to the table, including a start date for the diagram and a 'Today's Date' cell. Formulas are used to automatically generate subsequent dates. Cells are formatted to display only the day number or the weekday. The week start dates are merged and centered, and the column width is adjusted for better readability.

The process of creating task bars using conditional formatting is explained. A rule is set up to color cells green if the date falls within the task's start and end dates. The formula uses an 'AND' condition with specific cell references and dollar signs to ensure correct application across the table.

The conditional formatting is adjusted so that phases and milestones are not colored green. Additional conditions are added to the formula to exclude cells that contain phase or milestone data. New conditional formatting rules are then created for phases, giving them a distinct color.

Milestones are represented by a black diamond symbol using a direct formula in the cells. An 'IF' and 'AND' formula checks if a cell contains milestone data and if the date matches the milestone's date. If true, it outputs the letter 'u', which is then formatted with the 'Wingdings' font to display a diamond symbol.

A visual indicator for time progression is added by applying a darker shade of green to the task and phase bars for dates up to 'Today's Date'. The 'Actual Progress' column is formatted as a percentage, and data bars are added to visually represent the entered progress. The 'Planned Progress' column calculates the progress based on 'Today's Date' relative to the task's duration, also using data bars for visualization.

To simplify adding new rows and automatically replicating formulas, the entire data range is converted into an intelligent table. This ensures that when a new row is inserted, all relevant formulas for milestones, progress, and conditional formatting are automatically copied. The table's design is customized to match the original formatting.

The final Gantt chart is dynamic. By changing the 'Start Date Diagram' cell, the displayed date range shifts, allowing users to focus on specific periods. Furthermore, altering 'Today's Date' automatically updates the 'Planned Progress' and the color-coded time progression on the bars, providing an up-to-date overview of the project's status.