Hello

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.


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

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

thanks so much for your help! 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? for example all dates that are 3/4/2012, count them together, and use that number as the data plot and the date of 3/4/12 as the x axis on the graph?

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

thank you so much for your help! 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!

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

ahh i see. thanks for that suggestion, however this would be a manual filter every time i want to filter this. 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!! :)

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=9378A...121822A3%21326
for "sampledata"


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2