Macros to convert Data into date format and then plot in graph

[taimysho0]

Hello

I have exported a data set to an excel file and i am trying to write
macros that will convert the data in the cell into a date format, an
then use these dates to plot data points on a line graph. For example i
my attachment, the first cell has data of 120304-000564. The part i nee
converted is the first half before the hyphen which is 120304. thi
should translate into 2012-03-04. The first two digits represents th
year, followed by month and day. The second half of the numbers afte
the hyphen can just be removed.


once these numbers in column B are all converted into the proper dat
format, then i will need to activate a line graph creation from thes
data points. is this possible? thanks for any help!

+-------------------------------------------------------------------
|Filename: sampledata.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=946
+-------------------------------------------------------------------

 

[Claus Busch]

Hi,

Am Mon, 3 Mar 2014 21:09:34 +0000 schrieb taimysho0:

I have exported a data set to an excel file and i am trying to write a
macros that will convert the data in the cell into a date format, and
then use these dates to plot data points on a line graph. For example in
my attachment, the first cell has data of 120304-000564. The part i need
converted is the first half before the hyphen which is 120304. this
should translate into 2012-03-04. The first two digits represents the
year, followed by month and day. The second half of the numbers after
the hyphen can just be removed.

try:

Sub Test()
Dim LRow As Long

LRow = Cells(Rows.Count, 2).End(xlUp).Row
Range("B3:B" & LRow).TextToColumns Destination:=Range("B3"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, Other:=True, _
OtherChar:="-", FieldInfo:=Array(Array(1, 5), Array(2, 9)), _
TrailingMinusNumbers:=True
End Sub


Regards
Claus B.

 

[taimysho0]

Hi,

Am Mon, 3 Mar 2014 21:09:34 +0000 schrieb taimysho0:
-

try:

Sub Test()
Dim LRow As Long

LRow = Cells(Rows.Count, 2).End(xlUp).Row
Range("B3:B" & LRow).TextToColumns Destination:=Range("B3"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, Other:=True, _
OtherChar:="-", FieldInfo:=Array(Array(1, 5), Array(2, 9)), _
TrailingMinusNumbers:=True
End Sub


Regards
Claus B.

thanks so much for your help! now that i have these in date format, d
you know how i can do a count of all dates that are the same so i ca
plot them in a graph? for example all dates that are 3/4/2012, coun
them together, and use that number as the data plot and the date o
3/4/12 as the x axis on the graph

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Claus Busch]

Hi,

Am Tue, 4 Mar 2014 17:43:08 +0000 schrieb taimysho0:

now that i have these in date format, do
you know how i can do a count of all dates that are the same so i can
plot them in a graph?

in C3:
=COUNTIF(B:B,B3)
and copy down


Regards
Claus B.

 

[taimysho0]

Hi,

Am Tue, 4 Mar 2014 17:43:08 +0000 schrieb taimysho0:
-

in C3:
=COUNTIF(B:B,B3)
and copy down


Regards
Claus B.

thank you so much for your help! the last question is, in your dat
modify code above, how would i modify it so that the results displaye
are always within the last 90 days? so for example anything older tha
90 days gets deleted. thanks so much

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Claus Busch]

Hi,

Am Tue, 4 Mar 2014 22:38:28 +0000 schrieb taimysho0:

the last question is, in your date
modify code above, how would i modify it so that the results displayed
are always within the last 90 days? so for example anything older than
90 days gets deleted. thanks so much!

you can filter your date for the last 90 days. The chart shows only the
values in the visible cells. So you can keep all your data.


Regards
Claus B.

 

[taimysho0]

Hi,

Am Tue, 4 Mar 2014 22:38:28 +0000 schrieb taimysho0:
-

you can filter your date for the last 90 days. The chart shows only the
values in the visible cells. So you can keep all your data.


Regards
Claus B.

ahh i see. thanks for that suggestion, however this would be a manua
filter every time i want to filter this. my goal is to include al
these manual steps in a macros so that when a user clicks a button, th
raw data will be transformed to dates, and displaying only the last 9
days, and then from here, count the number of dates with same dates, an
then create a graph from this data. everything else should be delete
and only graph appearing. thats the goal of the macros. im trying t
include your code into the macros but having a hard time writing th
code for the >= last 90 days filter. thanks agian for any help!! :

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Claus Busch]

Hi,

Am Wed, 5 Mar 2014 17:19:15 +0000 schrieb taimysho0:

my goal is to include all
these manual steps in a macros so that when a user clicks a button, the
raw data will be transformed to dates, and displaying only the last 90
days, and then from here, count the number of dates with same dates, and
then create a graph from this data. everything else should be deleted
and only graph appearing. thats the goal of the macros. im trying to
include your code into the macros but having a hard time writing the
code for the >= last 90 days filter. thanks agian for any help!!

then create dynamic range names for x values and y values and create
with these names the chart. The dynamic names will always include the
last 90 days.
Please have a look:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "sampledata"


Regards
Claus B.