What controls the format of dates in an import file created by OC-Import?
Office Connector Import does not require you to enter and format date values using any specific format. This is because date values will be automatically re-formatted using the correct format when the import file is created. This provides you with the maximum flexibility in how you wish to have your information displayed while also maintaining compatibility with the import features.
The format of dates in an import file that is required by Sage Timberline Office is determined by a setting that is found under the Tools menu in the Options window. The Date Format selection determines the expected date format for both data-entry as well as importing. The selection made in this window is stored in a file named TS.INI that is located in your Sage Timberline Office data folder. Office Connector Import is designed to automatically apply this format (if possible) when creating the import file.
Office Connector Import will interpret a value as a date if it was entered as a date value in the worksheet. For example, if you enter a value of 5/31/2012 in a cell, Excel automatically interprets that as a date (based on it matching your Windows Regional Settings) and formats it as a date. This can be checked via the Format Cells window where you will see that a Date format has been applied. Note that date formats proceeded with an asterisk (*) automatically adapt to your Windows Regional Settings. Office Connector Import will also accept text values that contain dates (values formatted as text or entered with a proceeding single-quote). Text values will be interpreted as a date, again, by applying the Windows Regional Settings date format to interpret the value.
Office Connector Import - Date Reformatting
When Office Connector Import creates an import file, it will automatically reformat date values so that they are compatible with the date format required by Sage Timberline Office. To do this, it will first try to use the Date Format selection that is identified in the TS.INI file. For this to work, a Sage Timberline Office data folder must be associated with the workbook. This would normally be accomplished by making a connection to your Sage Timberline Office database via Office Connector Query or Office Connector Write. The associated data folder path is stored by Office Connector Query/Write in a custom property of the workbook and then used by Office Connector Import to determine the location of the TS.INI file.
In the event that no data folder is associated with the workbook, Office Connector Import will use the date format identified in your Windows Regional Settings. Specifically, the Short Date format will be applied. If the Short Date format found in Windows Regional Settings does not match the Date Format in Sage Timberline Office (under Tools / Options), you have two options:
Option 1 - Change the Short Date Format in Windows Regional Settings
You will need to change the date format so that it matches the date format identified in Sage Timberline Office under Tools / Options.
- Windows Vista and Higher: Click the Start menu, select Control Panel, and then click Clock, Language, and Region. Then click Region and Language.
Option 2 - Manually associate a data folder path with your workbook.
This will tell Office Connector Import where to find the TS.INI file that identifies the correct date format.
- Open the Document Properties window of the workbook
- Excel 2007: Click the Office Button, then click Prepare and then Properties. Then, in the bar (below the ribbon) click the Document Properties drop-down and click Advanced Properties.
- Excel 2010 and above: Click the File menu and then click Info. Then (toward the right), click the Properties drop-down and click Advanced Properties.
- Click the Custom tab.
- Check to see if the list of Properties (at the bottom) already contains an entry named TimberlineDataFolder. If so, click on it.
- In the Name box, type:
- In the Value box, type the path of your Sage Timberline Office data folder.
- Click Add (or Modify if the entry already existed)
- Click OK
NOTE - The JC Estimates import file format does not look to either the TS.INI file or to Windows Regional Settings for the date format. This file format uses a fixed date format that Office Connector Import will automatically use.