time display format

[waynecj7]

I am importing a large amount of data from an Excel spreadsheet using OLE.  I have everything working correctly except for the display of the time.  I have a named range in the spreadsheet that contains general information about the test.  One of the values is the time of the test in the format (mm/dd/yyyy hh:mm:ss AM).  It shows correctly in Excel, but when viewed after setting the value to a group property I create it is in the format of seconds (from jan 1, 1900 I assume).  I checked the settings for Diadem under "Settings->Options->General" and the time display format is set to #mm/dd/yyyy hh:nn:ss.  What do I need to do to get the time display to work.

Thanks,

Wayne

[Brad_Turpin]

Hi Wayne,

DIAdem data channels store datetime values as DBLs which count the number of fractional seconds elapsed since 0 AD.  DIAdem properties, on the other hand, store datetime values with an honest-to-goodness datetime data type.  So you need to first convert the number to a variant of subtype datetime before creating the property in DIAdem.  Depending on the formatting of your spreadsheet and  the method you're using to return the cell value from Excel, it may already be returned to your VBScript as a variant of subtype datetime.  If that is the case you just need to use the RootPropValSet(), GroupPropValSet(), or ChnPropValSet() functions to automatically translate that datetime subtype into a DIAdem datetime property.

If, on the other hand, you're returning the cell value as a variant of numeric subtype, then you will need to first convert the numeric value to a VBScript datetime variant with a integer offset and the CDate() function, or you can figure out how to change your Excel reading method to return a datetime variant instead of a numeric variant.

Brad Turpin
DIAdem Product Support Engineer
National Instruments

[waynecj7]

'This is an excerpt from the code

      PropRange = SheetName & "!Info"  
      PropArray = Sheet.Range(PropRange).Value
      Props = UBound(PropArray,1)
      For a = 1 to Props
        NewPropName = GroupPropCreate(GroupCount, PropArray(a,1), DataTypeString)
        Call GroupPropSet(GroupCount, NewPropName, PropArray(a,2))
      next 'a

This is the code I use to read in the two columns of data.  The first column is a descriptor that will become the property name and the second column is the value for the property.  Since the size of the data columns may change in length, I have found it very easy to use the range method to read the information into an array.  What are my options for setting the data type given that the data is a mostly strings with one as a date (see below).

Vehicle Test ID	FW-556
Vehicle Type	Honda / CRV AWD / 2005
Vehicle Description_1	VIN : SHSRD77885U335783
Vehicle Description_2	White 4-DOOR SUV
RigPar10 ID	STOKE
RigPar10 Description	STOKE RIG PARAMETERS - From 09/28/04
T-Spec10 ID	ROL201
T-Spec10 Description	Roll Test-Angle control - No Swaybars
Results ID	ROL201
Results Description	Roll Test - Angle control
Results Date-Time	16/09/05  10:10:31
Results Description_1	Loading: 2 68kg Front Occupants
Results Description_2	Handwheel Locked and P.A.S. On
Results Description_3	Front & Rear Anti-Roll Bar Disconnected

I just realized I wasn't exactly correct with the format.  The date is dd/mm/yy hh:mm:ss.  I noticed that when the date was not a valid US style date that Diadem left the value as a string.  Is there a way to convert the date to a US format from the EU style? 

I have tried finding the one property for "Results Date-Time" and setting the Group Property to "DataTypeDate" but, because of the EU style format mentioned above the result is very odd (01/09/0005 10:10:31).

Any thoughts would be appreciated.

Thanks,.

Wayne

[Brad_Turpin]

Hi Wayne,

What version of DIAdem are you using?  I recommended using the GroupPropValSet() command in my previous post, but that first became available in DIAdem 10.1.  This command both creates the proeprty if it doesn't already exist and it automatically assigns the correct property data type based on the variant subtype of the value you pass it, so it's very convenient to use.

Note that in your code you have the property data type hard coded to "DataTypeString".  This guarantees that all properties you create will be string properties in DIAdem.  I'm still not sure what the Excel formatting of the cell is that contains the "16/09/05  10:10:31" value.  That could either be an Excel datetime cell or an Excel string cell.  In the former case, I'll bet the PropArray(a,2) value you're getting is already correctly subtyped as a datetime, but the "DataTypeString" parameter guarantees that this values is converted to a string to fit into the string property you created.  In the latter case we will indeed need to first convert the value into a datetime variant.  In both cases you will need to either use GroupPropValSet or you will need to use an IF THEN statement to create string properties for string values and datetime properties for datetime values.

Brad Turpin
DIAdem Product Support Engineer
National Instruments

[waynecj7]

I am currently using v10.2 with the intention of going to v11 when I get a chance.  I guess I didn't notice the GroupPropValSet in your prior post.  I used that instead of the GroupPropCreat and GroupPropSet shown in the code above.  When I did that it made everything work just right.

Thanks for the help.