Adjusting Date Axes on Charts for Leap Year

[FracinDean]

This is a problem I have run into many times over the last 25 or so years (since Excel first came out). When I use a date variable on a chart axis, I often would like to have a fixed multiple of years (1 year, 2 years, etc.) as the major axis interval; however, the number of days varies for any interval that is not 4 years, because of leap year. The exception is during the centennial when there is no leap year. The only way that I know how to do this is to abandon the date variables altogether, and use an integer for the year plus the fraction of the year (YEARFRAC) to calculate the modulus. Then I can make the major interval 1. Does anyone know how to use some sort of date constants to assign the major interval as "one year" even though you are using serial dates on the axis? I'm happy to handle in either VBA or directly in Excel, if you can show me the way. Even if you don't have the answer for this one, thanks to all for reviewing the question, and thanks especially to the MVPs that are often so helpful.

 

[Jon Peltier]

What kind of chart is it? The built-in axis of a line chart will automatically show dates if it detects dates in the chart's X values. You can format the display of the axis however you want. In the screenshot below, the dates cover lots of years, including some leap years and the centennial, which is also a leap year because it's divisible by 400.

With the settings in the dialog/task pane, you can set the major and minor units. Always set the base unit to days. I set major unit of 2 year and minor of 6 months. This puts the major tick marks (with labels) at 1/1/1999, 1/1/2001, etc, that is January 1 of each odd year. The minor ticks are at January 1 and July 1 of every year. You can't resolve it at this scale, but the major tick marks have slightly different spacing because of the difference between 365 and 366 days per year, and the minor tick marks are also not equally spaced, because Jan-June (181 or 182 days) and Jul-Dec (184 days) have different numbers of days.

 

[ChemCougar82]

Thanks for the information. It's a scatter chart I'm concerned with. I am now curious to see how Excel will autoformat the date axis on the scatter chart if I truncate the dates to correspond to January 1 in two different years, and give it the date formatted inputs. I will reply later whether that worked or not. Not hopeful.
Meanwhile I am using a rather inelegant work around that I can live with. My major interval is 730.5 days. Minor is 365.25 days. Simple so far, but here's the non-obvious part. I am running my chart from Jan-2 to Jan-2 of different years. Sometimes my interval lands on Jan 1 and sometimes Jan-2. Then I format as MMM-YY. The ticks are all labeled as Jan-YYYY. Some are actually on 2-Jan, but that unimportant distraction is hidden by the formatting. If you run from Jan-1 to Jan-1, some intervals fall on Dec-31, and you can't solve the weird looking labels by changing the date format.

 

[ChemCougar82]

I tried using date-formatted serials that started and ended with Jan-1 using a scatter chart and it didn't recognize the full year intervals and do anything differently. When I set up my hypothetical date Range using the first day of every quarter using Auto-fill, it was smart enough to add a day for each first quarter in leap years in my data table, but the scatter chart axis generator did not auto adjust anything for leap year. It just used the same fixed interval for every time period on the chart.

 

[Jon Peltier]

Any reason not to use a line chart? The date axis will make it worthwhile.

 

[ChemCougar82]

Yes, my data have spatial variations in two dimensions not one. The line chart date axis convenience is not more important than using the correct spatial relationship between my data.

 

[Jon Peltier]

I don't understand. In a scatter chart, there are only two dimensions. Isn't one of them the date? There aren't enough other dimensions to represent spacial relationships.