# Difficulty in creating a complex bar chart including dates and comments

1. ## Difficulty in creating a complex bar chart including dates and comments

Hi Excel pros! I have some difficulties creating a barplot and would greatly appreciate your help.

Here is the story:
I produced different batches in my company. After the end of the production (date) I have 12 days to write the corresponding report.
My boss wants to see every week the current situation status with a barplot (i.e. if I am late with my reports and why (comments).

In the excel file attached to this post, I draw the perfect barplot using PowerPoint.

Here are my variables (what need to be included in the barplot):

- Batch number (Y-axis)
- Dates (X-Axis)

- Comment related to each batch (format: text)
- Lign showing the current date (today = 20.01.2022)

I would be extremely thankful if you could provide me with a solution.
I do have 0 experience with Excel, so your help will be hugely appreciated.

Please text me anytime if you have question.

Thank you Thank you for your help!

KR

Greg

2. ## Re: Difficulty in creating a complex bar chart including dates and comments

I ran into some difficulties here because of the regional differences in the settings between our systems for dates.

First of all, I converted the data range into an Excel Table for several reasons: Excel tables know how big they are so as you add and delete data from the table, the chart will change automatically.

I converted a new column in the table to create the End of Production Date into a date my system understands. Your end of production date might be a string and not a true date. If this is the case, you may have to convert it too. I suggest using this formula:
=IF(ISNUMBER([@[End of production]]),[@[End of production]],DATE(RIGHT([@[End of production]],4),MID([@[End of production]],4,2),LEFT([@[End of production]],2)))

Basically, it says that if what is in End of Production is a number (date) then use it otherwise parse it out an make the date. The parsing will work on both our systems. Column E is a real date, and I have it formatted to American style. You can format the cells to dd.mm.yyyy format. Likewise column C is a real date. This one I did choose to format to dd.mm.yyyy format.

yyyy might be jjjj on your system.

The chart itself is a clustered bar chart with 100% overlap. Where you have the extension shown as a line, I have a different colored bar. I am not sure I can get a line

Then I added data labels for the orange series. Initially these showed date values. I right clicked on these and selected format data labels. I clicked OFF the current selections and clicked on Value From Cells. Then I highlighted the values in Column D. Since this is an excel table, the chart "knows" that this range is actually the table column so when you add a new row, it will pick it up automatically.

It's not exactly what you want, but it is close.

You can delete the table rows. (Highlight the rows you want to delete, right click and select Delete > Table Rows. Then you can enter data in manually or copy and paste column A and B information in. The table will "remember" the formulas and copy them down. The chart will refresh automatically

3. ## Re: Difficulty in creating a complex bar chart including dates and comments

Hi Dflak,

I do not know how to express my gratitude.
Thank you so much for your detailed comment.

To be honest, you motivated me to learn more about excel.
And maybe one day I ll be the one helping other here

I wish you all the best and again, thank you!

Greeting from Switzerland!

4. ## Re: Difficulty in creating a complex bar chart including dates and comments

This forum helped me a lot. At first showing me how to do things. Then I learned a lot more when I started helping people out.

When I applied for my last job, I told them about the 7,000 posts I have hear. That was part of the reason they hired me.

I am glad to help and good luck.

5. ## Re: Difficulty in creating a complex bar chart including dates and comments

This one was still on my to do list.....
In my view it comes also close to the requested chart.
The data table however is a bit more complex.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1