Chart Dates without Blanks

bs0d

Well-known Member
Joined
Dec 29, 2006
Messages
622
I have daily data set (via query) which may have dates missing. I use a line-graph to view the data. Where dates are missing, the chart will draw a line between the two points, which is visually inaccurate. If the dates are missing, I'd like to see a gap instead, or zero's. So I'm looking for the most efficient way to add missing dates, so that nothing is plotted between the two dates where days are missing.

Here are two ideas:

• VBA Loop Method: Put query to sheet -- loop through rows evaluate and insert where dates are missing (slow or inefficient?).

• Query Method: Create a time table. Do a left join to include all rows from time table, and only those that match in the data query. Limit start and end dates to match min and max of targeted data.

Any feedback would be helpful.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

bs0d

Well-known Member
Joined
Dec 29, 2006
Messages
622
I'm trying the query idea and it may not work quite like I thought. Here's what I've got:

I created a ReadingDates tables with dates from 1/1/1900 to 12/31/2100. I created a query with a left join, matching the ReadingDates - date field with the date field from my data table. When I query the data table, it is for a specific entity.

I expected to see all dates from ReadingDates, and only the data on dates that exist in both tables for the entity I selected. However, I just see the typical results with dates missing. I think this is happening because there are no entity ID's in the ReadingDates table?

Is it still possible to get what I'm after through a query approach? Because I also need to limit the dates to start and end with the min(date) and max(date) of the entity-- so I don't have a query result from year 1900 to 2100.

Thoughts?
 
Last edited:

Forum statistics

Threads
1,136,274
Messages
5,674,774
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top