Log In
Register
Home
Active Member
July 23, 2024
Offline
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
See Answer
Moderators
January 31, 2022
Offline
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.
Answers Post
Active Member
July 23, 2024
Offline
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
Moderators
January 31, 2022
Offline
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.
Active Member
July 23, 2024
Offline
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.
Moderators
January 31, 2022
Offline
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).
1 Guest(s)
