Formatting dates on x axis of chart...!

Win XP HE
Excel 2002
Hi,
I have a chart with a simple y and x axis.  The x axis represents dates.
In sheet1 of the spreadsheet being used, the dates are in column A and in
each cell--for the moment is a formula: = sheet2, A2, A3, etc. (where the
dates are in this format: 12/20/2004.)
The result is (obviously) the same number and format 12/20/2004 in sheet1.
When I chart, for the life of me I cannot get rid of this format in the x
axis labels--I am usually able to define the label format in tha chart
itself but, nothing I do lets me change this format.
Even when unchecking the "link to source".
There must be some kind of error here, perhaps in the "text" format, or what
not.
What can I do to be able to change this format as I desire?
Tx a lot.
S



0
saturnin02
1/26/2005 5:20:33 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
227 Views

Similar Articles

[PageSpeed] 20

I shoud mention that the dates--whichhave been downloaded from a
database--are not properly formatted in the source sheet--i.e. they are
"stuck" as 12/20/2004 with no "numerical value" underlying--which probably
causes this "sticky" formatting problem.
How can I convert these unproperly formatted dates unti properly formatted
ones--the problem is that there is no real logical sequence to them--so I
cannot autofill.
Thanks a lot to you geniuses out there for a hint..... :)
S

saturnin02 wrote:
> Win XP HE
> Excel 2002
> Hi,
> I have a chart with a simple y and x axis.  The x axis represents dates.
> In sheet1 of the spreadsheet being used, the dates are in column A and in
> each cell--for the moment is a formula: = sheet2, A2, A3, etc. (where the
> dates are in this format: 12/20/2004.)
> The result is (obviously) the same number and format 12/20/2004 in sheet1.
> When I chart, for the life of me I cannot get rid of this format in the x
> axis labels--I am usually able to define the label format in tha chart
> itself but, nothing I do lets me change this format.
> Even when unchecking the "link to source".
> There must be some kind of error here, perhaps in the "text" format, or
> what not.
> What can I do to be able to change this format as I desire?
> Tx a lot.
> S


0
saturnin02
1/26/2005 5:36:41 PM
Well, thinking out loud sometimes is helpful.
I used the formula DATEVALUE to convert the 12/20/2004 to its proper
underlying numerical value, copy-pasted the value in column A and then
formatted as date as I wanted.
Perhaps not the best way but... works.
S
saturnin02 wrote:
> I shoud mention that the dates--whichhave been downloaded from a
> database--are not properly formatted in the source sheet--i.e. they are
> "stuck" as 12/20/2004 with no "numerical value" underlying--which probably
> causes this "sticky" formatting problem.
> How can I convert these unproperly formatted dates unti properly formatted
> ones--the problem is that there is no real logical sequence to them--so I
> cannot autofill.
> Thanks a lot to you geniuses out there for a hint..... :)
> S
>
> saturnin02 wrote:
>> Win XP HE
>> Excel 2002
>> Hi,
>> I have a chart with a simple y and x axis.  The x axis represents dates.
>> In sheet1 of the spreadsheet being used, the dates are in column A and in
>> each cell--for the moment is a formula: = sheet2, A2, A3, etc. (where the
>> dates are in this format: 12/20/2004.)
>> The result is (obviously) the same number and format 12/20/2004 in
>> sheet1. When I chart, for the life of me I cannot get rid of this format
>> in the x axis labels--I am usually able to define the label format in
>> tha chart itself but, nothing I do lets me change this format.
>> Even when unchecking the "link to source".
>> There must be some kind of error here, perhaps in the "text" format, or
>> what not.
>> What can I do to be able to change this format as I desire?
>> Tx a lot.
>> S


0
saturnin02
1/26/2005 5:47:41 PM
Sometimes you can just do:

Edit|Replace
what: / (slash)
with: / (slash)
replace all

And excel will reevaluate your input (and see it as dates).

saturnin02 wrote:
> 
> Well, thinking out loud sometimes is helpful.
> I used the formula DATEVALUE to convert the 12/20/2004 to its proper
> underlying numerical value, copy-pasted the value in column A and then
> formatted as date as I wanted.
> Perhaps not the best way but... works.
> S
> saturnin02 wrote:
> > I shoud mention that the dates--whichhave been downloaded from a
> > database--are not properly formatted in the source sheet--i.e. they are
> > "stuck" as 12/20/2004 with no "numerical value" underlying--which probably
> > causes this "sticky" formatting problem.
> > How can I convert these unproperly formatted dates unti properly formatted
> > ones--the problem is that there is no real logical sequence to them--so I
> > cannot autofill.
> > Thanks a lot to you geniuses out there for a hint..... :)
> > S
> >
> > saturnin02 wrote:
> >> Win XP HE
> >> Excel 2002
> >> Hi,
> >> I have a chart with a simple y and x axis.  The x axis represents dates.
> >> In sheet1 of the spreadsheet being used, the dates are in column A and in
> >> each cell--for the moment is a formula: = sheet2, A2, A3, etc. (where the
> >> dates are in this format: 12/20/2004.)
> >> The result is (obviously) the same number and format 12/20/2004 in
> >> sheet1. When I chart, for the life of me I cannot get rid of this format
> >> in the x axis labels--I am usually able to define the label format in
> >> tha chart itself but, nothing I do lets me change this format.
> >> Even when unchecking the "link to source".
> >> There must be some kind of error here, perhaps in the "text" format, or
> >> what not.
> >> What can I do to be able to change this format as I desire?
> >> Tx a lot.
> >> S

-- 

Dave Peterson
0
ec357201 (5290)
1/27/2005 12:22:33 AM
OK, I'll try that as well.
Tx for the tip.
S

Dave Peterson wrote:
> Sometimes you can just do:
>
> Edit|Replace
> what: / (slash)
> with: / (slash)
> replace all
>
> And excel will reevaluate your input (and see it as dates).
>
> saturnin02 wrote:
>>
>> Well, thinking out loud sometimes is helpful.
>> I used the formula DATEVALUE to convert the 12/20/2004 to its proper
>> underlying numerical value, copy-pasted the value in column A and then
>> formatted as date as I wanted.
>> Perhaps not the best way but... works.
>> S
>> saturnin02 wrote:
>>> I shoud mention that the dates--whichhave been downloaded from a
>>> database--are not properly formatted in the source sheet--i.e. they are
>>> "stuck" as 12/20/2004 with no "numerical value" underlying--which
>>> probably causes this "sticky" formatting problem.
>>> How can I convert these unproperly formatted dates unti properly
>>> formatted ones--the problem is that there is no real logical sequence
>>> to them--so I cannot autofill.
>>> Thanks a lot to you geniuses out there for a hint..... :)
>>> S
>>>
>>> saturnin02 wrote:
>>>> Win XP HE
>>>> Excel 2002
>>>> Hi,
>>>> I have a chart with a simple y and x axis.  The x axis represents
>>>> dates. In sheet1 of the spreadsheet being used, the dates are in
>>>> column A and in each cell--for the moment is a formula: = sheet2, A2,
>>>> A3, etc. (where the dates are in this format: 12/20/2004.)
>>>> The result is (obviously) the same number and format 12/20/2004 in
>>>> sheet1. When I chart, for the life of me I cannot get rid of this
>>>> format in the x axis labels--I am usually able to define the label
>>>> format in tha chart itself but, nothing I do lets me change this
>>>> format.
>>>> Even when unchecking the "link to source".
>>>> There must be some kind of error here, perhaps in the "text" format, or
>>>> what not.
>>>> What can I do to be able to change this format as I desire?
>>>> Tx a lot.
>>>> S


0
saturnin02
1/27/2005 8:55:20 PM
Reply:

Similar Artilces:

baby charts
I need to find a chart that I can download to chart my baby's eating times & changing times too? I think you must have an idea of what you want, if not place the times across the top 0 to 23 for hours or place 12:00 AM into cell B1 and 1:00 AM into C1 and then select B1:B2 and use the fill handle to fill across In cell A1: 'Date \ Time In cell A2: 09/01/2005 format as mm/dd/yyyy ddd or whatever format you use. This is for US entry and format. use the fill-handle to fill down to end of month File, page setup, Headers and Footers customer header: cen...

Save copy of active sheet
I use the code below to save a copy of my Workbook in a specific file path (testing to see if the folders already exist along the way) with a specific, data dependent file name. It works, but I'd like to tweak it a bit and I'm not sure how to write the code. I'd like to save only the active sheet instead of the whole workbook. I'd also like to save only the values and formats to the new file, not the formula and macros. Anyone have any advice on how to accomplish this? Here's my current code: Sub SaveName() If Not Len(Dir("g:\users\one\" & Range("h6&...

Date conversion question
Am trying to understand why is that when I have a variable MaxDate (undeclared) and is has a value of 38697 and if I use =TEXT(MaxDate,"mm/dd/yy") I get the correct value of 12/12/09 but if I use function =CDATE(MaxDate) I get 12/11/2005 as an output? Also the format command didn't work either to convert MaxDate with the code =format(MaxDate, "mm/dd/yy") as it also gave 12/11/2005 when the correct output was 12/12/09. I am trying to understand this. Thanks, Chet Assuming you are using XL2003 or lower... if you click to Tools/Options = on the menu bar, th...

Chart #3
I'm trying to crate a chart where to information in the cells can be typed on a slant Hi Quiana, Having difficulting understanding your question. Maybe you could provide a little more detail. Quiana wrote: > I'm trying to crate a chart where to information in the cells can be typed on a slant -- Cheers Andy http://www.andypope.info To display a cell's text on a slant: 1. Select the cell 2. Choose Format>Cells 3. Select the Alignment tab 4. In the Orientation setting, drag the red diamond, or select a number of degrees 5. Click OK Quiana wrote: > I'm...

Excel charts won't appear or preview. Help!
Hello, In Excell 2003 I cannot get my charts or graphs to appear or preview. I was able too at first. But I accidentally hit right click and then clear several times after having created template line chart for the first time. This subsequently one at a time removed different aspects of my chart until my chart was gone. And now i cannot get the charts to appear again. Not even a preview. Thank you in advance foe your help and advice. This problim has been solved. Thank you. ...

Charting 3 columns help
I am working on a staffing chart with three columns: Staffing Time Incident 6 3 1234 15 9 1234 I would like a graph to show the "Time" on the left the "Incident" on the bottom and Staffing in the chart I have racked my brain and have not figured out how, to make the chart work that is :o) Do I need to re-arrange the columns? a different way? Any help would be greatly appreciated. Steve In your brief sample, Incident has no variation. How do you want staffing to appear in the cha...

actual vs plan chart
I have a table of data, let's call it ACTUAL SALES by month, similar to following: Business Unit West East North South Jan09 20 30 15 10 Feb09 10 20 30 40 Mar09 14 16 12 10 I have a table that looks like it, for PLANNED SALES data, with various numbers for each business unit's planned target for the same periods for which I have actuals. What I want to do is (ideally) create a stacked bar chart of the Actuals for each region (ie, the height of the stack = total actual sales), ...

Report Writer and effective dated tables
Hi, I am new to using report writer. I am trying to write a report that will just pull back the latest effecitve dated row by employee. A SQL statement that can do this would be something like: SELECT MAX(effective_date) FROM myTable WHERE effective_date <= Now(); I can not figure out how to do this in Report Writer. Any suggestions? Anyone know if this is possible? "badabing" wrote: > Hi, I am new to using report writer. I am trying to write a report that will > just pull back the latest effecitve dated row by employee. > > A SQL statement that can do thi...

Re: How can I change the color of negative bubble in bubble chart
Actually, you have to define your second color, click OK twice, so the series displays the gradient, then double click the series again,on the patterns tab, select the fill for positive values, and hit OK. (The series must actually display the gradient for a moment in order to remember the second color). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message news:... > Double click on the series, on the Patterns tab, ...

Chart Y-Axis Title
My Y-Axis titles are cut-off and do not display the entire title. It does not matter what size font I use. Why is this doing this? Why? Because it is a well-known bug. We look forward to Excel 2007! It seems to be related to video since some have the problem and others do not. Add some periods (dots) to the end of the title and then format them the same colour as the background so that those without the problem will not see them best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "PelleritoConsultants" <PelleritoConsultants@discussions.micros...

Org Chart:1 Manager 2 Sections
How do I divide two work sections of employees under one manager? I tried arranging the first set of staff under a vertical alignment and wrapping a team frame around them, but when I add positions to create the 2nd team, they are aligned the same. If I arrange the employees horizontally, members are randomly assigned to the left or right, breaking the team arrangments. ...

Due Date in Statement of accounts??
Hi , Can i print the sales statement of account showing the DUE DATE ? If yes, kindly please help me to create the field. Thanks in advance. ...

format numbers #2
I have a list of audio books in an excel 2003 spreadsheet. One column should show the duration of the book in this format: 8 hrs 23 mins. But what shows in the column is a 5 digit number, such as 54463 as an example. I cannot figure out how to format this column to translate this number into the hrs and mins of the actual book. Could someone please tell me how to do this? Thanks for your help Joanne Joanne Where does this number come from? It does not seem to relate to 8 hours 23 minutes at all (eg it isn't the total minutes for example or seconds). What is the source of this informa...

Scrollbar on Chart Jumps to Left when Chart is Clicked
I fiddled around with 'offset' and a scrollbar, and now I can change the range of my chart just by clicking on the scrollbar. However, there is a problem with placement. If I click on the chart, and move the scrollbar to where I want it, when I click *OFF* the chart, the scroll bar moves about double the distance from top left that it was when the chart was clicked. It's like the scrollbar thinks I'm doubling the size of the chart when I click off the chart. If I position the scrollbar past halfway from top left, when I click off the chart the scrollbar total...

Offset secondary axis
Hi all.. 1. How do I move secondary axis so that it'll be displayed together with the primary axis? 2. Is it possible to offset secondary axis so that it seems to be continuing from primary axis? the reason is because i'm plotting a value for 'H/D' which goes from 0 to infinity,.. but I'll break it at 'H/D'=5, and start with its reciprocal 'D/H'.. so the H/D = 5 equals to D/H = 0.2 and H/D = infinity equals to D/H = 0 pls kindly help... or any other suggestion... Grayfox ------------------------------------------------------------------------ Posted ...

Outlook 2007 Edit Business Card Format
I've just started using Outlook 2007 with the Business Contact Manager. I'd like to edit the format of the Business Card in both Outlook and Business Contact Manager. I have discovered how to edit an individual card but not how to edit all the existing cards and future cards. Can this be done and if so how? Thanks, Bob It can't be done; that functionality just isn't built into Outlook. I = expect, though, that some enterprising programmer will produce an = inexpensive tool or sample application to apply a particular format to = all EBCs.=20 --=20 Sue Mosher, Outlook ...

sort dates
hI My dates use the system dd/mm/year if i press the sort button.. it will sort by day->month->year I want to sort by year->month->day how do i do this?? thank -- Message posted from http://www.ExcelForum.com The best thing to do is to store your data as real Excel dates. Then they will always sort properly, regardless of which format you display the date in. As you have numbers, rather than dates, sorting isn't so easy. The choices are: 1. Create a Date field (=Date(year,month,day)) and sort on that. 2. Split out the day, month and year, then sort on the three separa...

Custom Format text XX:XXXX:XX
I would like to have text custom formatted so that if a 8 character words is typed, it is automatically broken up like this XX:XXXX:XX. This works just fine with numbers with a ##\:####\:## custom format, but it will not work for text. Any suggestions? -- mustard ------------------------------------------------------------------------ mustard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20017 View this thread: http://www.excelforum.com/showthread.php?threadid=346023 Number formatting only works on numbers. But you could use a little macro waiting for y...

Date in the query is always short date
I want to capture the LONG date from a query, so I create a parameter for the date (mm ddd yy) . In its properties, it's format is long date, BUT it always shows up the short date. WHY? Dates are stored as decimal number counting days from 12/31/1899 midnight with time as the decimal fraction of a day. Formats are just different ways to display the information. So you got to set the format. In design view of the query click the field of the grid that has the datetime field, right click, scroll down and select Properties. Click 'Format' and then in the pull down...

format of emailed excel file
In excel 2003 when I email a a particulat worksheet in a workbook I lose all the formating when it is recieved in Outlook 2003. Is there anyway to preserve the excel "look"? Thnaks, Craig S ...

Conditional format based on data from another column
Hi Yesterday I asked about the possibility of conditional formatting a range of 20 random numbers into 2 different formats. Thank you for that, but now it seems that 3 of my 20 numbers are now requiring a different cell to be highlighted in yet another colour. Currently the situation is as follows:- Column G has a list of random numbers ranging from 1-20, with columns either side pertaining to the random number located in the relevant cell in Column G. It now appears that 3 numbers in this random list do not require highlighting in Column G and have amended the condition formattin...

LIne chart problem
I'm trying to set up a simple supply and demand chart in Excel 2007. I've done this many times with past versions but can't get this to work in 2007. Basically I want the price of the product on the y-axis (can't MS call it that, please?) then supply to run upward with the number of products on the x-axis and demand to run downward. I can't get the price on the y-axis no matter what I try. I've looked everywhere online, but switch row/column and using a secondary axis don't work. This should be so simple. Argh! -- Thanks, Shane Devenshire "Fru...

Format a cell to display as all caps
Is there any way to format a cell so that text entered will always display in caps? Hi Not Directly !......but you can use the UPPER function. If your data is in A1, put =UPPER(A1) in A2. This will convet whatever you type in A1 to Uppercase in A2 HTH Michael "WAF" wrote: > Is there any way to format a cell so that text entered will always display in > caps? No. You cannot format a cell to display caps. You can use event code in the worksheet to change the text to caps when you enter it. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column &g...

Pls Hlp
I have a list of suppliers in column A, the values of how much spent in column B, the percentage of the total in column C. I want to create a column waterfall chart that will show the largest value starting at 0, the next value being added above the first value, etc. I also wish to display both the value and the percentage of the whole as labels above each column bar. Help! -- Thanks! Dee ...

Dynamic Chart #5
Hi, How can I keep a personal format in a dynamic chart, when I update it? All the time, it returns to the original format. Thanks Renato -- Renato ----------------------------------------------------------------------- Renato8's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3060 View this thread: http://www.excelforum.com/showthread.php?threadid=50329 By Dynamic Chart, do you mean Pivot Chart? Pivot Charts are notorious about removing any formatting when they are updated. You could make a regular chart from a pivot table's data. Select a blank cell ...