Excel Gantt Chart: Showing cost amount on each bar

[Leox1992]

Hi Tech Pros,

Need help.

I am using an excel gantt chart made up using conditional formatting.

It is work fine as the project timeline.

But I want to upgrade it by showing the cost of each activity on each bar, and this must sense the length of the bar automatically and place the figure in the center.

Help me with the conditional formatting formula or either solution.

 Let me know if you need Excel file.

Email Id: email address removed for privacy reasons  

Thank You

[#2]

@Leox1992 

Creating a Gantt chart with automatic cost labels on each bar can be achieved using a combination of Excel formulas, data manipulation, and conditional formatting. Here is a step-by-step approach to achieve this:

Assuming you have the following data setup:

• Column A: Activity Names
• Column B: Start Dates
• Column C: Durations (in days)
• Column D: Costs

1. Calculate End Dates: In Column E, calculate the end dates for each activity using the formula:

Copy code

=B2+C2-1

This gives you the end date of each activity.

1. Create Gantt Chart:
• Create a Gantt chart using conditional formatting, where you highlight the cells corresponding to the activity duration.
• For each cell, set the formula to determine whether the current date falls within the start and end dates of an activity.
2. Calculate Midpoint Dates: In Column F, calculate the midpoint dates for each activity using the formula:

=B2+(C2/2)

This will give you the middle date of each activity's duration.

1. Place Cost Labels:
• In Column G, use the following formula to check if the current date is equal to the midpoint date of an activity:

=IF(F2=TODAY(),"$"&D2,"")

This formula checks if the current date (TODAY()) is equal to the midpoint date, and if so, it displays the cost of the activity. Adjust the formatting as needed.

1. Conditional Formatting for Cost Labels:
• Apply conditional formatting to the cells in Column G.
• Use a formula to determine when the cost label should be displayed. For example:

=G2<>""

• Format these cells to center-align and format the cost label.

By using these steps, you can automatically calculate and display cost labels in the center of each Gantt chart bar. The midpoint date helps in determining where to place the cost label, and the conditional formatting ensures that the label is only displayed when the current date matches the midpoint date.

Please note that this approach involves some manual data entry and might not dynamically update as the chart changes. For more advanced solutions, you might need to consider using VBA (macros) or specialized Gantt chart software. My knowledge of this topic is limited, but since no one has answered it for at least one day or more, I entered your question in various AI. The text and the steps are the result of various AI's put together.

My answers are voluntary and without guarantee!

Hope this will help you.

[#3]

@Leox1992 

One option may be to use a Stacked Bar Chart to achieve the Gantt-like display.  With a chart you could use data labels to pull 'cost' by using 'values from cells'.