Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Thorny problem graphing XAxis dates
Sorry, I'm not really sure how to state this question succinctly. I have data in an MSAccess Db re medications and the time\date and amounts taken. Mads are entered only in the db on days when they are taken yet I need the graph to show ALL days including days not taken. That is, days when the amount is inferred to be zero because no data appears for that day. Sorry, that's probably as clear as mud. Currently, I'm using VBA code to select the data, build a spreadsheet and then build a graph from the spreadsheet I built. The problem is that it (obviously) graphs only the days meds are taken, not days in between. I did something like this many years ago using brute force coding but it is a torturous coding process and doesn't seem very elegant. I think I may have seen a way to do a SQL select statement that accomplishes what I want but my thinker is getting old and sometimes unreliable. Does anyone have any ideas on this? Thank you, Don in Redmond |
#2
|
|||
|
|||
Don -
You need to make sure your dates are recognized by Excel as numerical values, not as text labels. Coming from Access, it's not unlikely that they'd be imported as text. You can use Text To Columns to try to fix a nonnumeric column of dates. Then you need to make sure these dates are used as the category values. It sounds like this is not an issue in your case. Finally, you need to make sure that your chart has a time-scale category axis. If the dates are recognized as numeric, Excel does this automatically, which is why I started with my first paragraph. Fix the dates, and the axis should take care of itself. (You'll have to specifically change the axis of an existing chart that was first made with text labels.) - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Don wrote: Sorry, I'm not really sure how to state this question succinctly. I have data in an MSAccess Db re medications and the time\date and amounts taken. Mads are entered only in the db on days when they are taken yet I need the graph to show ALL days including days not taken. That is, days when the amount is inferred to be zero because no data appears for that day. Sorry, that's probably as clear as mud. Currently, I'm using VBA code to select the data, build a spreadsheet and then build a graph from the spreadsheet I built. The problem is that it (obviously) graphs only the days meds are taken, not days in between. I did something like this many years ago using brute force coding but it is a torturous coding process and doesn't seem very elegant. I think I may have seen a way to do a SQL select statement that accomplishes what I want but my thinker is getting old and sometimes unreliable. Does anyone have any ideas on this? Thank you, Don in Redmond |
#4
|
|||
|
|||
you need to make sure that your chart has a time-scale category
axis. Thanks Jon, it's exactly what I need. My data was of the correct data type, but I did not select 'Time Scale' for the XAxis. Regards, Don On Wed, 21 Sep 2005 07:52:26 -0400, Jon Peltier wrote: Don - You need to make sure your dates are recognized by Excel as numerical values, not as text labels. Coming from Access, it's not unlikely that they'd be imported as text. You can use Text To Columns to try to fix a nonnumeric column of dates. Then you need to make sure these dates are used as the category values. It sounds like this is not an issue in your case. Finally, you need to make sure that your chart has a time-scale category axis. If the dates are recognized as numeric, Excel does this automatically, which is why I started with my first paragraph. Fix the dates, and the axis should take care of itself. (You'll have to specifically change the axis of an existing chart that was first made with text labels.) - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Don wrote: Sorry, I'm not really sure how to state this question succinctly. I have data in an MSAccess Db re medications and the time\date and amounts taken. Mads are entered only in the db on days when they are taken yet I need the graph to show ALL days including days not taken. That is, days when the amount is inferred to be zero because no data appears for that day. Sorry, that's probably as clear as mud. Currently, I'm using VBA code to select the data, build a spreadsheet and then build a graph from the spreadsheet I built. The problem is that it (obviously) graphs only the days meds are taken, not days in between. I did something like this many years ago using brute force coding but it is a torturous coding process and doesn't seem very elegant. I think I may have seen a way to do a SQL select statement that accomplishes what I want but my thinker is getting old and sometimes unreliable. Does anyone have any ideas on this? Thank you, Don in Redmond |
#5
|
|||
|
|||
Thanks Tushar, you understand me perfectly correctly and your suggestion is right on. Thank you, Don On Wed, 21 Sep 2005 18:56:32 -0400, Tushar Mehta <tm_200310@tushar_hyphen_mehta_dot_see_oh_em wrote: If I understand you correctly, after you have created the chart, select it, then select Chart | Chart Options... | Axes tab. In there make sure the x-axis type is 'Timescale'. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem calculating Days/Hours etc between 2 dates | Excel Discussion (Misc queries) | |||
Conditional formatting with dates formula problem. | Excel Discussion (Misc queries) | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) | |||
due dates | New Users to Excel | |||
Problem with dates | Excel Worksheet Functions |