Date Format + Graphs

[Guest]

Hi,

I have data in the following format:
DATE VALUE
10012004 100.1
11012004 120.6
12012004 112.0
15012004 115.8
17012004 118.9

Now, I cannot change this data on the excel sheet. I will need to use this
data to draw a graph which I am doing using VBA. I know that the date in the
above format can be converted to DDMMYYYY by using say [
=DATEVALUE(LEFT(B9,2)&"/"&MID(B9,3,2)&"/"&RIGHT(B9,4)) ]
How, can I get the graph that gets generated to display dates in DD/MM/YYYY
format ?

Thanks in advance.

 

[Tom Ogilvy]

convert your data using your formula so you will be graphing actual dates.

then once the graph is created, right click on the axis and select format
Axis. then pic the format you want to show or do it with a custom format.

If you need code, turn on the macro recorder while you do it manually.

 

[Guest]

Wonder if this is an elegant way to do it.
I will redefine the data that I am working with, before I go on to say what
I did with it.
Data on the excel sheet:
----------------------------
DATE VALUE
7012004 100.5
8012004 98.4
9012004 99.2
10012004 100.1
11012004 120.6
12012004 112.0
15012004 115.8
17012004 118.9

Since, the data on the input sheet is not editable.
-1- copy the column data on to another sheet, before pasting it, change the
column data format to 'date' and also set the formula for the cells as
say( =DATEVALUE(LEFT(B9,2)&"/"&MID(B9,3,2)&"/"&RIGHT(B9,4)) )
-2- The data gets pasted in the required (date DD/MM/YYYY ) format.
-3- Now, plot the graph
-4- The resulting, graph will display date (rather, the plot will be value
v/s date )

Issue!
-------
And here is where I need further help:
If you noticed the date data, if the date is less than 10th of any month i.e
say
9/01/2004 ( 9012004 ) then the above dosen't work as easily. Now, the formula
changes to ( =DATEVALUE(LEFT(B9,1)&"/"&MID(B9,2,2)&"/"&RIGHT(B9,4)) )

How do I incorporate this programmatically ?

 

[Tom Ogilvy]

=if(len(b9)=8, DATEVALUE(LEFT(B9,2)&"/"&MID(B9,3,2)&"/"&RIGHT(B9,4))
,DATEVALUE(LEFT(B9,1)&"/"&MID(B9,2,2)&"/"&RIGHT(B9,4)))

--
Regards,
Tom Ogilvy

Wonder if this is an elegant way to do it.
I will redefine the data that I am working with, before I go on to say what
I did with it.
Data on the excel sheet:
----------------------------
DATE VALUE
7012004 100.5
8012004 98.4
9012004 99.2
10012004 100.1
11012004 120.6
12012004 112.0
15012004 115.8
17012004 118.9

Since, the data on the input sheet is not editable.
-1- copy the column data on to another sheet, before pasting it, change the
column data format to 'date' and also set the formula for the cells as
say( =DATEVALUE(LEFT(B9,2)&"/"&MID(B9,3,2)&"/"&RIGHT(B9,4)) )
-2- The data gets pasted in the required (date DD/MM/YYYY ) format.
-3- Now, plot the graph
-4- The resulting, graph will display date (rather, the plot will be value
v/s date )

Issue!
-------
And here is where I need further help:
If you noticed the date data, if the date is less than 10th of any month i.e
say
9/01/2004 ( 9012004 ) then the above dosen't work as easily. Now, the formula
changes to ( =DATEVALUE(LEFT(B9,1)&"/"&MID(B9,2,2)&"/"&RIGHT(B9,4)) )

How do I incorporate this programmatically ?

Hi,

I have data in the following format:
DATE VALUE
10012004 100.1
11012004 120.6
12012004 112.0
15012004 115.8
17012004 118.9

Now, I cannot change this data on the excel sheet. I will need to use this
data to draw a graph which I am doing using VBA. I know that the date in the
above format can be converted to DDMMYYYY by using say [
=DATEVALUE(LEFT(B9,2)&"/"&MID(B9,3,2)&"/"&RIGHT(B9,4)) ]
How, can I get the graph that gets generated to display dates in DD/MM/YYYY
format ?

Thanks in advance.

 

[Myrna Larson]

Three possible fixes:


=DATEVALUE(LEFT(RIGHT("0"&B9,8),2)&"/"&MID(RIGHT("0"&B9,8),3,2)&"/"&RIGHT(B9,4))
=DATE(MOD(B9,10000),B9/1000000,MOD(B9/10000,100))
=DATE(RIGHT(B9,4),LEFT(B9,IF(LEN(B9)=8,2,1)),MID(B9,IF(LEN(B9)=8,3,2),2))

Wonder if this is an elegant way to do it.
I will redefine the data that I am working with, before I go on to say what
I did with it.
Data on the excel sheet:
----------------------------
DATE VALUE
7012004 100.5
8012004 98.4
9012004 99.2
10012004 100.1
11012004 120.6
12012004 112.0
15012004 115.8
17012004 118.9

Since, the data on the input sheet is not editable.
-1- copy the column data on to another sheet, before pasting it, change the
column data format to 'date' and also set the formula for the cells as
say( =DATEVALUE(LEFT(B9,2)&"/"&MID(B9,3,2)&"/"&RIGHT(B9,4)) )
-2- The data gets pasted in the required (date DD/MM/YYYY ) format.
-3- Now, plot the graph
-4- The resulting, graph will display date (rather, the plot will be value
v/s date )

Issue!
-------
And here is where I need further help:
If you noticed the date data, if the date is less than 10th of any month i.e
say
9/01/2004 ( 9012004 ) then the above dosen't work as easily. Now, the formula
changes to ( =DATEVALUE(LEFT(B9,1)&"/"&MID(B9,2,2)&"/"&RIGHT(B9,4)) )

How do I incorporate this programmatically ?

Hi,

I have data in the following format:
DATE VALUE
10012004 100.1
11012004 120.6
12012004 112.0
15012004 115.8
17012004 118.9

Now, I cannot change this data on the excel sheet. I will need to use this
data to draw a graph which I am doing using VBA. I know that the date in the
above format can be converted to DDMMYYYY by using say [
=DATEVALUE(LEFT(B9,2)&"/"&MID(B9,3,2)&"/"&RIGHT(B9,4)) ]
How, can I get the graph that gets generated to display dates in DD/MM/YYYY
format ?

Thanks in advance.