Chart Time Axis Showing Only 0:00

**gtcwalker6** · 09-11-2014, 05:09 PM

Greetings,

I have a chart that has an x-axis that is time-based. The time is linked to cells (in another worksheet) where the full date and time are calculated by adding two cells (one date and one time). For example, column A is date and column B is time. Column C is equal to A+B, giving [dd/mm/yyy hh:mm] format. The chart's x-axis is then linked to column C.

The axis labels on the chart only display 0:00. However, when you click on any individual data point, it provides the correct value of the date and time [mm/dd/yyy hh:mm]. Also, when you look at the data selection, the Horizontal (Category) Axis Labels show the correct date and time values.

I have tried changing the Axis Options > Number format to different styles of Time and Date. The axis either displays "0:00" for every major unit or "1/0/00 0:00...1/5/00 0:00...1/10/00 0:00" for every major unit.

Appreciate the help.

CW

**MrShorty** · 09-11-2014, 05:14 PM

Is this a line chart (with date axis) or a scatter chart? I suspect that you have a line chart with a date axis. The smallest "division" in a line chart is 1 day, so the divisions on the chart are the beginning of the day (0:00). When you need the chart to distinguish both date and time, you will generally need a scatter chart.

If that isn't it, then post a worksheet so we can see what you have.

**gtcwalker6** · 09-11-2014, 08:38 PM

This is actually a scatter chart and the 0:00s do not correspond to noon or midnight on any day. They are spread about 8 hours apart. For instance, the first 0:00 label corresponds to a data point at 9/9/2014 21:00 and the second 0:00 label corresponds to a data point at 9/10/2014 05:00.

Unfortunately, the sheet is confidential (and would take a lot of effort to make it acceptable to post), so I cannot post it.

I was hoping that someone had a similar experience and had a quick fix.

Thank you for the reply.

**gtcwalker6** · 09-11-2014, 09:56 PM

I think I've found that the issue is regarding the formula. Is there a function to put around the formula to ensure that Excel recognizes it as a date-time value?

**MrShorty** · 09-12-2014, 11:06 AM

This is where not having an example of your data will make it really difficult to debug your solution. What format is your date/time data? Are they text strings? Date/time serials? Other??

I might suggest a review of this (http://www.cpearson.com/excel/datetime.htm ) to review how Excel wants to handle date/time data. Here is Excel 2010's list of date/time functions: http://office.microsoft.com/en-us/ex...402.aspx?CTT=1 Perhaps you could go back to the point before "adding" the time to the date and use the datevalue and timevalue functions to convert them to serial numbers. Then perform the date/time arithmetic using Excel's serial numbers.

I don't know if it will help, but this is a copy of a spreadsheet with random data that I created to illustrate for someone else the diifference between scatter and line and how they treat date/time serials. I have specifically formatted the x axis of the scatter chart so that the major division axis labels will show different times of day. I cannot create a similar effect on the line chart because a line chart cannot work divisions smaller than 1 day. Perhaps you can put a sampling of your data into this spreadsheet to illustrate your question.