X-Axis Date

Hi,
I am making a graph with dates along the X-axis.  The data was exported from 
Access and is in the format m/dd/yyyy h:mm:ss AM/PM.  Excell doesnt seem to 
recognize this as a data format and it wont graph properly.  I have tried 
"format cells", but it doesnt work.  What is really strange is that when I 
click in the cell and then press enter, it converts to a date format that is 
recognized by excell.  I have over 2 years of hourly data, so I dont really 
want to click in every cell.  Anyone have any suggestions?
0
Amber (25)
7/9/2009 3:58:02 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
628 Views

Similar Articles

[PageSpeed] 46

Dates in Excel are actually numbers (see 
http://www.cpearson.com/excel/datetime.htm)
Since editing converts you data to a date, it might be that you have 
imported text not numbers

Try this:
Select and copy and empty cell (you have copied the value 0)
Select all the dates that need fixing; use Edit | Paste Special with Add 
specified
Excel often converts text to numbers (when it is possible) with this trick
Tell us if it worked
best wishes
-- 
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Amber" <Amber@discussions.microsoft.com> wrote in message 
news:9E0A70D9-E5F5-422D-B5DC-ECC2EAF72D23@microsoft.com...
> Hi,
> I am making a graph with dates along the X-axis.  The data was exported 
> from
> Access and is in the format m/dd/yyyy h:mm:ss AM/PM.  Excell doesnt seem 
> to
> recognize this as a data format and it wont graph properly.  I have tried
> "format cells", but it doesnt work.  What is really strange is that when I
> click in the cell and then press enter, it converts to a date format that 
> is
> recognized by excell.  I have over 2 years of hourly data, so I dont 
> really
> want to click in every cell.  Anyone have any suggestions? 


0
bliengme (657)
7/9/2009 6:49:35 PM
Yes it worked!
Thank you!

"Bernard Liengme" wrote:

> Dates in Excel are actually numbers (see 
> http://www.cpearson.com/excel/datetime.htm)
> Since editing converts you data to a date, it might be that you have 
> imported text not numbers
> 
> Try this:
> Select and copy and empty cell (you have copied the value 0)
> Select all the dates that need fixing; use Edit | Paste Special with Add 
> specified
> Excel often converts text to numbers (when it is possible) with this trick
> Tell us if it worked
> best wishes
> -- 
> Bernard V Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
> remove caps from email
> 
> 
> "Amber" <Amber@discussions.microsoft.com> wrote in message 
> news:9E0A70D9-E5F5-422D-B5DC-ECC2EAF72D23@microsoft.com...
> > Hi,
> > I am making a graph with dates along the X-axis.  The data was exported 
> > from
> > Access and is in the format m/dd/yyyy h:mm:ss AM/PM.  Excell doesnt seem 
> > to
> > recognize this as a data format and it wont graph properly.  I have tried
> > "format cells", but it doesnt work.  What is really strange is that when I
> > click in the cell and then press enter, it converts to a date format that 
> > is
> > recognized by excell.  I have over 2 years of hourly data, so I dont 
> > really
> > want to click in every cell.  Anyone have any suggestions? 
> 
> 
> 
0
Amber (25)
7/9/2009 7:19:01 PM
Great! Thanks for the feedback
-- 
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Amber" <Amber@discussions.microsoft.com> wrote in message 
news:3209A22A-EE50-430A-A7C1-47FCDC805BD7@microsoft.com...
> Yes it worked!
> Thank you!
>
> "Bernard Liengme" wrote:
>
>> Dates in Excel are actually numbers (see
>> http://www.cpearson.com/excel/datetime.htm)
>> Since editing converts you data to a date, it might be that you have
>> imported text not numbers
>>
>> Try this:
>> Select and copy and empty cell (you have copied the value 0)
>> Select all the dates that need fixing; use Edit | Paste Special with Add
>> specified
>> Excel often converts text to numbers (when it is possible) with this 
>> trick
>> Tell us if it worked
>> best wishes
>> -- 
>> Bernard V Liengme
>> Microsoft Excel MVP
>> http://people.stfx.ca/bliengme
>> remove caps from email
>>
>>
>> "Amber" <Amber@discussions.microsoft.com> wrote in message
>> news:9E0A70D9-E5F5-422D-B5DC-ECC2EAF72D23@microsoft.com...
>> > Hi,
>> > I am making a graph with dates along the X-axis.  The data was exported
>> > from
>> > Access and is in the format m/dd/yyyy h:mm:ss AM/PM.  Excell doesnt 
>> > seem
>> > to
>> > recognize this as a data format and it wont graph properly.  I have 
>> > tried
>> > "format cells", but it doesnt work.  What is really strange is that 
>> > when I
>> > click in the cell and then press enter, it converts to a date format 
>> > that
>> > is
>> > recognized by excell.  I have over 2 years of hourly data, so I dont
>> > really
>> > want to click in every cell.  Anyone have any suggestions?
>>
>>
>> 

0
bliengme (657)
7/10/2009 11:56:33 AM
Reply:

Similar Artilces:

Office v.x Test Drive Timed Out After Two Weeks
I bought my wife a Macintosh G4 tower. I purchased Office v.x at the same time, but UPS delivered the Office package separately. We found the test drive software on the hard drive and used it while waiting for the Office software. I was waiting for a friend to come back from vacation to get help exporting e-mail from Entourage so I could uninstall the test drive and install the real software. Something happened--my guess is it was configuring the system to use local time (it defaulted to Pacific)--that caused the test drive to suddenly stop working. Is there some way to get the ...

Y-axis tick marks
Hi all, I'm pretty knew to Excel VBA and charting. I have a simple histogram chart on a worksheet and the data ranges are 1 to 100. So Excel automatically puts the Y-axis tick marks at 0, 20, 40, 60, 80, 100. But I only want three tick marks to show on the Y-axis (0, 50, 100). How can I do that? Also is there a way to put a horizontal line at the 50 y-axis tick mark that will show on top of my chart? My users want to be able to quickly see the 50 line (i.e. which histogram bars extend above it, and which ones don't reach it). It would be great if I could programmaticall...

Date Picker
Hello, Is there a DatePicker in Excel 2007 so I can pick a date when I focus on a cell? If not, where can I get one? Thanks, Miguel Look here: http://www.rondebruin.nl/calendar.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "shapper" <mdmoura@gmail.com> wrote in message news:776296c8-83d8-467e-aefb-c31b24446bf7@b15g2000yqd.googlegroups.com... > Hello, > > Is there a DatePicker in Excel 2007 so I can pick a date when I focus > on a cell? > > If not, where can I get one? > > Thanks, > Miguel ...

Word Perfect 5.x converter fails after security update MS09-073
It appears that on December 8, 2009, a security update (MS09-073) for the Office 2003 File Converter Pack was issued. Now, when I attempt to open a Word Perfect 5.x file in MSWord 2003 on my Windows XP machine, I receive an error message that states, "File appears to be corrupted." Is there a registry change or other action I can take to restore my ability to convert and open WP 5.x files? Microsoft has a habit of trashing old converters with 'security' updates. You can download the old converter from my web site. How long it will work for is anyone's gues...

Make Table Query
I have a Macro and at the end of the Macro I have a Make Table Query that I would like to automatically save as todays date in the current database. Does anybody have an idea on how I can do this? On Wed, 06 Jun 2007 11:40:24 -0700, dswiders@gmail.com wrote: >I have a Macro and at the end of the Macro I have a Make Table Query >that I would like to automatically save as todays date in the current >database. Does anybody have an idea on how I can do this? A date is just a field value. Saving today's date as a Table is almost certainly VERY bad design. Why do an expensive, awk...

Problem with xy scatter with dates
I am trying to create an x-y scatter chart where both the x values and the y values are dates. I highlight the 2 adjacent columns of date data and click on Insert>Scatter. Instead of a scatter graph, I get a line chart with 2 lines (the two data columns). The x values on the graph are simply a count of the data points as in a line chart. If the 2 columns of data are not dates, I get the correct scatter chart, but not if the x-values are dates in which case I get a line chart. How can I get a scatter chart with both x-values and y-values as dates? You could select just the Y val...

Clustered column with secondary y-axis
I am comparing two attributes...say height and mass...of six different supplied products...say widgets. I would like to make a clustered column chart so that each product is side-by-side on the x-axis and that the attributes are indicated on the y-axis. The attributes, though, have different scales. For example, heights may range from 20 to 40 cm and masses may range from 250 to 500 grams. So if they are on the same y-axis, it is difficult to discern the difference in heights of the products (without perhaps dividing all masses by 10 which I'm not to keen on). No problem. I add ...

Value axis
in my worksheet the data format 100% (no decimals) and in the value axis it shows as 100000000000% - how can I change this to show as a max of 3 digits. Thank you This makes little sense, all those zeros. What is the actual value in the sheet, and what is the actual number format in the sheet? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Shevvie" <Shevvie@discussions.microsoft.com> wrote in message news:86E7DCFA-9461-479F-AA14-A0E8B516E8F4@microsoft.com... > in my work...

Office X Registration
Hello, does anyone know how to tell if Office X is registered? Also, is it possible to change a product over to someone elses' name once it is registered? Thanks a lot! I don't know of a way to tell if a specific copy of Office has been registered. It doesn't really matter since registration is optional. Since the EULA allows one transfer of the software, yes, it is possible to change it over to someone else's name. That person can access the registration page if she/he wishes by going to "Online Registration" under the Entourage, Excel, Word, or PowerPoint menu....

How do I write the print date on data row?
Hi, I need to run a report once a month. When I run that report, I select all unprinted record / row and print them on the report. When I print the record / row I want to date stamp the row with the Report's print month (Jan-2009, Feb-2009, etc). This will do two things for me. One, this field will flag that the row has been printed. And two, I can recreate any prior month report by select all rows with that month's "print month". I know I can run a Update Query before the report is run, but then I don't know for a fact the that record was printed...

Excel 2003 - VBA
Does anyone have and idea how to create the date of the last day of the previous month? Craig Hi Craig =Dateserial(year(date), month(date), 0) HTH. Best wishes Harald "Craig Brandt" <brandtcraig@att.net> wrote in message news:yvVxk.22131$89.8111@nlpi069.nbdc.sbc.com... > Does anyone have and idea how to create the date of the last day of the > previous month? > > Craig > > =TODAY()-DAY(TODAY()) -- __________________________________ HTH Bob "Craig Brandt" <brandtcraig@att.net> wrote in message news:yvVxk.22131$89.8111@nlpi069.nb...

Dates are not displayed in ComboBox
I have a From ComboBox and a To ComboBox on a Form; cboFrom and cboTo. The Row Source is this: SELECT DISTINCT SharePrices.DateTime FROM SharePrices ORDER BY SharePrices.DateTime; The SQL is this: SELECT DISTINCT SharePrices.DateTime FROM SharePrices ORDER BY SharePrices.DateTime; Nothing is showing in either ComboBox; all I see is a whole bunch of blanks. I just did this in another form in another Access database. I can’t figure out why it’s not working for me now. I’m using Access 2007. I’d sincerely appreciate any insight with this. Thanks! Ryan -- Ryan--...

documents in transaction inquiry should default to date
When first entering the Payables Transaction Inquiry - Vendors screen, the documents section is defaulted to "by document number." It would be extremely helpful to have the option of being able to change the Documents default to "by Document Date." ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and the...

Conditional formatting with dates formula problem.
Hello. I appreciate help on this topic. I'm very new to excel's conditional formatting capabilities and I need help on the following: I have a worksheet where I am using columns A and B to be fashioned into a type of "reverse" library checkout card; I want to flag when 120 days have passed since an item has been checked out. All cells are blank with the exception of the formatting applied to cells in column A. For example, Condition 1 on cell A1 has the formula: =IF(ISBLANK(B1),(A1-TODAY())<120) **I've also tried =IF(ISBLANK($B1),($A1-TODAY())<120) My objecti...

Anomaly skewing Axis units
Hi. I am graphing numbers around $18 million. I have an anomaly that is around $55 billion. I've seen a graph where the bar with the $55 billion has a "slash" through it, and then the axis matches the $18 million numbers, with a "primary driver" note for the $55 b. I'm not sure how to create this and would appreciate any suggestions. Cheers, Stephanie Jon's page should help you http://peltiertech.com/Excel/Charts/BrokenYAxis.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Stephanie...

Year to date sales history of inventory
How can this system provide me with cumulative sales numbers of a particular item ie: How many total gym shorts did we sell in 12 months? Detailed Sales Report with a custom date range. In the body of the report look at the top line (Overall) that will give you the answer you seek my child. Now go caterpillar blossom into a butterfly. Rob "KatieH" wrote: > How can this system provide me with cumulative sales numbers of a particular > item ie: How many total gym shorts did we sell in 12 months? Musta got a new batch o glue, huh? (g) KatieH To pick up where Rob started...

Access autonumber field changes to date format when Excel imports
When importing Access data into Excel, the autonumber field in Access is chenged to date format instead of a general number format. I could run a macro to change the number format but this only happens on some Vista machines, not all. Any suggestions as to why this is happening? ...

Finding X- & Y-intercepts of lines crossing curves
I created some charts with multiple data series by specifying certain points on the curves. THe series are displayed as lines. Now I want to draw (or specify the Y coordinates of) a horizontal line on the chart, & have Excel tell me the X intercepts where the horizontal line crosses each curve. Ditto for drawing a vertical line & reading Y intercepts Any ideas Thanks!! Hi Dale, Maybe you adapt my example of intersections. http://www.andypope.info/charts/intersection.htm Cheers Andy Dale wrote: > I created some charts with multiple data series by specifying certain points on...

How to plot chart with 2 different y-axis with a common x-axis?
I want to plot on the same chart y-values with different units. The x-axis will be the same for both. How do I do it? "ARC" <ARC@discussions.microsoft.com> wrote in message news:8ADB9688-277A-4E36-A3CB-5E3BFA708EA8@microsoft.com... >I want to plot on the same chart y-values with different units. The x-axis > will be the same for both. How do I do it? For whichever series you want on the second Y-axis, Format Data Series/ Axis Select secondary axis -- David Biddulph ...

Exporting As Text with X Number of Spaces Between Data
Is there a way to export data in a MS Excel spreadsheet as text and define the amount of spaces between the data being exported? The number of spaces need to vary. This concept is the opposite of importing text as "Fixed Width" and creating column break lines. Instead, I'd like to export columns into a text file and specify the amount of spaces between the text that is being exported from each column. The amount spaces in the text file needs to be different between each set of column data. Thanks! One way: Say you have 5 columns, and you want 2 spaces between A &...

Too many points on horiz axis
I'm working with an MS Chart/Graph in an Access form. I realize this is an Excel newsgroup, but I can't get any response in the Access newsgroup. Hence terms such as "form" "rowsource," etc., may not seem familiar. But I think the question relates simply to the graph object. I hope someone can help. Or, perhaps, point me to a more appropriate newsgroup or forum? TIA LAS I have a graph on a form. The form has no datasource. The graph's rowsource is a table/query: SELECT qryLevelsAll.Week_of, Avg(qryLevelsAll.Level) AS AvgOfLevel FROM qryLevelsAll GRO...

Convert imported text to date time
I've read all the posts on this but I can't seem to find one that gives me the answer with a time field. I am importing from Excel as a .cvs and the date time comes in as a text field with 12/09/2009 0830. I need to convert this from text to an actual date/time. mm/dd/yyyy hh:mm preferably in military time. I need to calculate the minutes from Actual Departure time to Actual Arrival time. You guys will forever be my hero if you can give me the directions on doing this. Thanks! Cindy I pieced this together rapidly ad it seems to work. Function ConvDT(sFullDT) ...

Excel newbie question about auto inserting date
Sorry for the trivial question, but I've spent an hour in excel help, and online trying to find the answer. I want to make a worksheet where I can type in the month and year in the upper left corner, and the worksheet fills the top row with the days (1, 2, 3.....31), and then the row below that the days of the week (mon, tues, wed.....). I'm certain this program can do it, but I can't find the answer anywhere. Thanks. -- aznick ------------------------------------------------------------------------ aznick's Profile: http://www.excelforum.com/member.php?action=getinf...

Date of row change (auto entry date)
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Is there any way that column AA (or whatever column) can have a date appear according to the last time any value was created/changed in that row (say in all columns BEFORE column AA in that row)? <br><br>It used to be possible in Excel for PC (don't remember how I did it, but it might have also involved macro, although I think it was just a clever use of date() or now() ... <br><br>Ideas? <br><br>Thanks! <br><br>reeedeeer I don't believe there is any way to d...

Julian Dates?
Hi - i bet this is simple - but how do i convert NOW() ... or TODAY() into a julian date like 31206 (day 312, year 06) thanks Worksheet and macro solutions posted by Myrna Larson 1999-06-11 http://groups.google.com/groups?oi=djq&ic=1&selm=an_488468851 --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "ElBozo" <kobebus@verizon.net> wrote in message news:FMgih.5223$4Q6.1916@trnddc06... > Hi - i bet this is simple - but how do i conv...