Hi,
I am trying to create simple line chart which should show the trend for multiple items over time.
Columns F, G and H are constant (they are the target).
Other items (M, N, O,...) change over time but ideally should be around targets. I added only M and N for now.
Coumns K and L (week and date) just show when the values are updated in the table (every week).
I created the chart but X and Y axis shows strange dates.
I think formated everything as Date.
Can someone please help? What am I doing wrong?
I am attaching the excel file.
Thank you.
Vladimir
When I open your file, the dates in columns M:X are in fact recognized as text. They all show as "yyyy-mm-dd", whereas the other (real) dates are like "yyyy/mm/dd".
Use Find and Replace (Ctrl-H) to find all the hyphens "-" and replace them by a forward slash "/". Then you will be working with real dates, and some lines will appear. Not sure though it those are what you want them to be.
Then, I believe that you want the X-axis populated with the dates from column L. But you had column L as the source of a line. I changed that. See attached.
Does this make more sense? If not, please clarify and perhaps you can provide a sketch of what the graph should represent.
Ok, thanks a lot for reply, I had no idea about this date formats with - and /. Looks great now.
Just two additonal questions:
1. When I do Format cell -> and then select as Date, why isn't that enough? I see that format uses yyy-mm-dd which in this case is wrong. Where does this difference between - and / come from? Should I format cells in some other way?
2. You are right, X-axis should be populated with the dates from column L. What do you mean by "you had column L as the source of a line"? and what was the change you did here?
Thank you.
Vladimir
Regarding 1, your local system settings dictate how dates are recognized. So, your Windows environment is set a format yyyy/mm/dd. If you now enter or copy/paste 'dates' in an other format, Excel sees them as texts. Then you can not just set the cell to Date in order to make it a date.
Regarding 2, your original file has a series called Date that points to a range of dates in L. After fixing the date formatting problem, that series displayed (for me) as a blue line from the bottom left to the top right. I removed it and pointed the X-axis labels to that range in stead.
I see, ok. How did you replace - with / in the L column? Since it has formula and in the formula there is minus sign which would get replaced with / (division) and then I get incorrect result.
There's nothing wrong with the dates in column L (neither with the ones in F:H). It's the ones in columns M:X. These are the ones where I replaced - with / using Find & Replace (Ctrl-H).
Ok, thank you very much for the help.
