View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.charting
Tonso Tonso is offline
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