| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Format + Graphs
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date Format + Graphs
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. -- Regards, Tom Ogilvy "Rain" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date Format + Graphs - solved, but; Need further help
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 ? "Rain" wrote: 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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date Format + Graphs - solved, but; Need further help
=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 "Rain" wrote in message ... 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 ? "Rain" wrote: 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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date Format + Graphs - solved, but; Need further help
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(B 9,IF(LEN(B9)=8,3,2),2)) On Sat, 15 Jan 2005 10:07:04 -0800, "Rain" wrote: 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 ? "Rain" wrote: 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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date Format + Graphs - solved, but; Need further help
Tom, Myrna, Thanks for the inputs.
"Myrna Larson" wrote: 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(B 9,IF(LEN(B9)=8,3,2),2)) On Sat, 15 Jan 2005 10:07:04 -0800, "Rain" wrote: 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 ? "Rain" wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| permanent conversion of 1904 date format to 1900 date format | Excel Worksheet Functions | |||
| change date format dd/mm/yyyy to Julian date format? | Excel Worksheet Functions | |||
| Why does the date show up wrong in Excel graphs? | Excel Discussion (Misc queries) | |||
| code to convert date from TEXT format (03-02) to DATE format (200203) | Excel Programming | |||
| Change a date in text format xx.xx.20xx to a recognised date format | Excel Programming | |||
Linear Mode
