DPlot Forum Index DPlot
http://www.dplot.com
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Dates

 
Post new topic   Reply to topic    DPlot Forum Index -> Excel
View previous topic :: View next topic  
Author Message
rborn



Joined: 18 May 2004
Posts: 16
Location: New Jersey

PostPosted: Tue May 18, 2004 3:56 pm    Post subject: Dates Reply with quote

In the demo version I created a CSV file from my Excel data and imported it into DPlot. Everything seemed to work fine. When I got the registered version it includes a macro for XYYY or XYXY data which could be a real time saver. The problem is, it gives me an error when my X column is of this type of data: "04/23/04 09:04:18.0". This is a Date/Time stamp for each point of data, but I get a Type Mismatch error when I use the XYYY macro. I assume it needs an integer, but is there another way around this?

Thanks
Back to top
View user's profile Send private message
DPlotAdmin
Site Admin


Joined: 24 Jun 2003
Posts: 2290
Location: Vicksburg, Mississippi

PostPosted: Tue May 18, 2004 6:12 pm    Post subject: Reply with quote

I tried this just now and it worked (with one show-stopping problem, see below) so my guess is it's just a formatting problem. Select your date/time column in Excel, then select Format>Cells. My guess is "Text" is selected under "Category". If instead you select "Custom" and "m/dd/yy h:mm:ss" under "Type" it should work.

But as mentioned above, there's a problem. The Add-In uses DPLOTLIB.DLL to pass data, and DPLOTLIB.DLL currently takes only single-precision floating point numbers. For most purposes this is sufficient, but for date and time groups that's not enough precision to distinguish seconds... actually it's limited to about 5 minutes precision on today's date. A double-precision version is in the works, should be available before too long.

You can get around this limitation for now by saving your Excel sheet as a .CSV file and opening in DPlot with file type D. That should correctly interpret your date and time values and keep the precision you need.

And please let me know if I'm incorrect about the formatting. If I'm not, I'd appreciate it if you'd send me your Excel file.
_________________
David Hyde
support@dplot.com
Back to top
View user's profile Send private message Send e-mail Visit poster's website
rborn



Joined: 18 May 2004
Posts: 16
Location: New Jersey

PostPosted: Wed May 19, 2004 7:26 am    Post subject: Reply with quote

Since the samples are taken every 10 seconds, I think the precision issue will be a problem, so I guess I will do it via a CSV file. I'm assuming I could knock off the date oart and it would work ok? This is probably more work than it's worth though and teh CSV file would probably be quicker.

BTW, the date/time field was set to General. I changed it to the Custom format you suggested, but I had to add the "ss" as all I had was mm/dd/yyy h:mm, it still gave me the same error.

I will send you the Excel file for your review.

Thanks

Russell
Back to top
View user's profile Send private message
DPlotAdmin
Site Admin


Joined: 24 Jun 2003
Posts: 2290
Location: Vicksburg, Mississippi

PostPosted: Wed May 19, 2004 8:51 am    Post subject: Reply with quote

Russell,
Thanks for the file. The first thing I noticed was that I'll need new glasses if I'm going to look at your 10 pt font very often Very Happy

I apparently don't know as much about Excel as I thought I did. As far as VBA is concerned your date-time column is just text, not dates and times. And back on the sheet, if you modify the formatting you'll notice that it doesn't change the appearance of the date at all - for example if you use 'yyyy' you'll still get a 2 digit year. On the other hand, =MONTH(A3) extracts the correct month from cell A3. So I'm not real sure what's going on here.

If you save your file as .CSV and reopen it in Excel, then the Add-In function XYYY works as expected.

You can make your .xls file work if you replace those dates and times with... well... dates and times. In cell F3 I typed
=DATE(YEAR(A3),MONTH(A3),DAY(A3))+TIME(HOUR(A3),MINUTE(A3),SECOND(A3))
then double-clicked the fill handle to fill in the column. Copy that column, then Edit>Paste Special>Values in column A. There's surely an easier way, but this will work.
_________________
David Hyde
support@dplot.com
Back to top
View user's profile Send private message Send e-mail Visit poster's website
rborn



Joined: 18 May 2004
Posts: 16
Location: New Jersey

PostPosted: Wed May 19, 2004 11:05 am    Post subject: Reply with quote

That seems to work, but we still have the precision problem I am assuming. I plotted the "Date & Time" and just the "Time" alone and it seems the "Date & Time" dropped some points, or combined them since it probably rounded some of the values (due to your explanation of the precision issue)? Overall it doesn't change the "essence" of the plot, but it's technically not correct.

I am going to use your function but only for the time, or format the column for time only then graph it. I will note the date in the Title of the graph, makes more sense to do it this way anyway since it's daily data.

One more thing...I have 14 days of graphs to do and 4 graphs for each day. I also need to merge two Excel files into one graph. I know I can Append data inside DPlot, but is this somehow possible using the macro? I acutually separated the data, since there may be more data for a value. I should have combined the data into one file for each graph and use the XYXY macro, but I didn't know this was available when using the demo version. I could go back and do this if it makes most sense. Bascially I might have a Excel file with 20 XY points for one item and another Excel file with 30 XY points that I want graphed together.

Also, since there are 4 of the same graphs for each day, is there a way to use the Preferences and Excel macros together, or do I need to make them all look the same inside DPlot after he graph is developed from the macro.
Back to top
View user's profile Send private message
DPlotAdmin
Site Admin


Joined: 24 Jun 2003
Posts: 2290
Location: Vicksburg, Mississippi

PostPosted: Wed May 19, 2004 11:28 am    Post subject: Reply with quote

Quote:
That seems to work, but we still have the precision problem I am assuming.


Yes, that is correct. I'll hopefully have a double-precision version available soon that will handle this properly.

Quote:
I plotted the "Date & Time" and just the "Time" alone and it seems the "Date & Time" dropped some points, or combined them since it probably rounded some of the values (due to your explanation of the precision issue)?


Yes.

Quote:
One more thing...I have 14 days of graphs to do and 4 graphs for each day. I also need to merge two Excel files into one graph. I know I can Append data inside DPlot, but is this somehow possible using the macro?


No, the Add-In will always produce a new plot. The simplest way to do what you want inside DPlot is to use Edit>Move/Copy Curves. This preserves legends, line styles, and symbol styles from the source plot.

Quote:
Also, since there are 4 of the same graphs for each day, is there a way to use the Preferences and Excel macros together, or do I need to make them all look the same inside DPlot after he graph is developed from the macro.


From Excel the only way to do that would be to either modify the Add-In to tell DPlot to read a preferences file or write a short VBA routine to do the same thing after running the Add-In function. In both cases you would use DPlot_Command with a [GetPreferences(...)] command. The trouble with not doing this inside the Add-In is that the first argument to GetPreferences is the document index (1 for the first plot, 2 for the 2nd plot, etc.). That number is returned by DPlot_Plot (used by the Add-In to create your plot). If you have multiple plots open then it's a bit of trouble to determine the appropriate value to use unless you have that return value from DPlot_Plot.

Probably the easiest thing to do is make a DPlot macro to read your preferences file and assign a hotkey to that macro.
_________________
David Hyde
support@dplot.com
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    DPlot Forum Index -> Excel All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group