Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I eliminate non-used dates (weekends) from a chart?
My data uses only week day dates. The chart that results from it is adding
weekend dates even though they don't appear in the range for the X axis. It leaves gaps in the chart as a result. |
#2
|
|||
|
|||
Nanoking,
One option is to reference your X-axis series with a formula that converts the date to text but still leaves the "text" date looking like a date. For example, assuming the data below starts in cell A1, convert column A to text by using this formula: =TEXT(A2,"mm/dd/yy") col A col B col C Orig. Date Text Date Data 01/01/04 01/01/04 5 01/02/04 01/02/04 4 01/03/04 01/03/04 5 In the formula view, column B looks like =TEXT(A2,"mm/dd/yy") =TEXT(A3,"mm/dd/yy") =TEXT(A4,"mm/dd/yy") Change your X-axis reference from column A to column B. The chart will read the X-axis dates as text and will not leave gaps. ---- Regards, John Mansfield http://www.pdbook.com "nanoking" wrote: My data uses only week day dates. The chart that results from it is adding weekend dates even though they don't appear in the range for the X axis. It leaves gaps in the chart as a result. |
#3
|
|||
|
|||
Thanks, John. That's what I'm doing. I was hoping for a more elegant fix.
I had hoped there was a formatting element I was overlooking in either the chart or the data. As it is, I still want to have the date form and so use two columns; one formatted for text. I appreciate your response. Perhaps in the next edition MS will recognize a need for the creation of a workday calendar. Something like MS Project would be nice with a basic setting that eliminates weekends. That way you could also fill a column and not have weekend dates included. Cheers! JDL "John Mansfield" wrote: Nanoking, One option is to reference your X-axis series with a formula that converts the date to text but still leaves the "text" date looking like a date. For example, assuming the data below starts in cell A1, convert column A to text by using this formula: =TEXT(A2,"mm/dd/yy") col A col B col C Orig. Date Text Date Data 01/01/04 01/01/04 5 01/02/04 01/02/04 4 01/03/04 01/03/04 5 In the formula view, column B looks like =TEXT(A2,"mm/dd/yy") =TEXT(A3,"mm/dd/yy") =TEXT(A4,"mm/dd/yy") Change your X-axis reference from column A to column B. The chart will read the X-axis dates as text and will not leave gaps. ---- Regards, John Mansfield http://www.pdbook.com "nanoking" wrote: My data uses only week day dates. The chart that results from it is adding weekend dates even though they don't appear in the range for the X axis. It leaves gaps in the chart as a result. |
#4
|
|||
|
|||
Probably it's quicker to go to Chart Options on the Chart menu, click on
the Axes tab, and change from the Time Scale (or Automatic) option to Category under the Category X Axis. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ nanoking wrote: Thanks, John. That's what I'm doing. I was hoping for a more elegant fix. I had hoped there was a formatting element I was overlooking in either the chart or the data. As it is, I still want to have the date form and so use two columns; one formatted for text. I appreciate your response. Perhaps in the next edition MS will recognize a need for the creation of a workday calendar. Something like MS Project would be nice with a basic setting that eliminates weekends. That way you could also fill a column and not have weekend dates included. Cheers! JDL "John Mansfield" wrote: Nanoking, One option is to reference your X-axis series with a formula that converts the date to text but still leaves the "text" date looking like a date. For example, assuming the data below starts in cell A1, convert column A to text by using this formula: =TEXT(A2,"mm/dd/yy") col A col B col C Orig. Date Text Date Data 01/01/04 01/01/04 5 01/02/04 01/02/04 4 01/03/04 01/03/04 5 In the formula view, column B looks like =TEXT(A2,"mm/dd/yy") =TEXT(A3,"mm/dd/yy") =TEXT(A4,"mm/dd/yy") Change your X-axis reference from column A to column B. The chart will read the X-axis dates as text and will not leave gaps. ---- Regards, John Mansfield http://www.pdbook.com "nanoking" wrote: My data uses only week day dates. The chart that results from it is adding weekend dates even though they don't appear in the range for the X axis. It leaves gaps in the chart as a result. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get Excell to sort dates that range from 1800 to 1900's | New Users to Excel | |||
How to Calculate Dates without counting the weekends | Excel Worksheet Functions | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) | |||
due dates | New Users to Excel | |||
I get wrong dates when i paste from a different sheet into a new s | Excel Discussion (Misc queries) |