A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Charts and Charting in Excel
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Time is not correct on x-axis



 
 
Thread Tools Display Modes
  #1  
Old July 14th 15, 12:43 PM posted to microsoft.public.excel.charting
Tonso
external usenet poster
 
Posts: 90
Default Time is not correct on x-axis

In XL 2103, I copy data from a webpage and paste into a worksheet. One of the fields has time as, for example, 07/13/2015 19:00:01. I have a formula in another column - (VLOOKUP(HOUR(V18),How!P$3:Q$27,2,FALSE) to return the time as 19, 20,21, and use the lookup table to get 7pm, 8pm, etc. In the formula V is the column with the time, and P3:Q27 is the following table..
0 12am
1 1am
2 2am
3 3am
4 4am
5 5am
6 6am
7 7am
8 8am
9 9am
10 10am
11 11am
12 12pm
13 1pm
14 2pm
15 3pm
16 4pm
17 5pm
18 6pm
19 7pm
20 8pm
21 9pm
22 10pm
23 11pm

This returns the correct hour as 7am, 3am, etc. In the cells it works fine. but on the x-axis it shows up at 1,2,3,4 etc. I have to click on the a-axis and re-select the data (7am, 8am, etc.) in order for it to show up as hour/am/pm on the graph.
In summary, the x-axis currently reads 1,2,3, etc. and i want 7pm, 8pm, etc.., which i get only after click on the x-axis and re-selecting the data.

What must I do? Is there a better way to get the time from the time column (07/13/2015 19:00:01 for example)?

Thanks,

Tonso
Ads
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Overlapping time (X) axis labels with auto axis options Paul Kraemer Charts and Charting in Excel 1 February 8th 09 03:44 AM
Subtract hours from a time and get the correct time casey Excel Worksheet Functions 1 June 22nd 08 08:41 PM
xy-scatter-labels disappear when x-axis is changed into time-axis (XL07) Holger Gerths Charts and Charting in Excel 8 June 5th 08 04:25 PM
TO CREATE A LINE CHART WITH RATE IN Y AXIS & REAL TIME IN X AXIS vijaicbe Charts and Charting in Excel 4 November 9th 06 10:16 AM
x axis not showing correct values Micayla Bergen Charts and Charting in Excel 1 August 28th 05 01:57 AM


All times are GMT +1. The time now is 10:31 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.