Import Field Date Format

Dates in imported and exported files can be represented in a number of ways. Please bear in mind that what you see in a spreadsheet may not be the actual layout of your dates. To see the actual layout, open your file in a text editor, such as Notepad.

Then, when you have determined the actual layout of the dates that you are importing, select the option below that matches your file's date format.

Dates will be converted into the Collect! internal date format as they are imported. Then they will be displayed correctly in accounts, contacts, transactions etc. However, they must be read correctly from the file as the import is being run, so your choice below must accurately reflect the format of the date as it appears in the file you are importing.

Import Field Date Options

These options override the date options set in the Import Date Options form and apply only to this specific field. By default, dates are MM/DD/YY.

The notes field is considered a text field, so special steps are required to indicate that you are importing a date into the notes. Set the date format on this form to match the format in the data file.

MMDDYY

Radio button: MMDDYY
This is MonthDayYear

If you determine that the actual layout of the dates given in the imported file is in the format MM/DD/YY,

For example:
Date: April 9, 2001
Formatted as 04/09/2001 04/09/01 04092001

Then select the MMDDYY radio button.

YYMMDD

Radio button: YYMMDD
This is YearMonthDay

If you determine that the actual layout of the dates given in the imported file is in the format YYMMDD,

For example:
Date: April 9, 2001
Formatted as 2001/04/09 01/04/09 20010409

Then select the YYMMDD radio button.

DDMMYY

Radio button: DDMMYY
This is DayMonthYear

If you determine that the actual layout of the dates given in the imported file is in the format DDMMYY,

For example:
Date: April 9, 2001
Formatted as 09/04/2001 09/04/01 09042001

Then select the DDMMYY radio button.

YYYYDDD

Radio button: YYYYddd
This is YearDayofYear

If you determine that the actual layout of the dates given in the imported file is in the format YYYYddd,

For example:
Date: April 9, 2001
Formatted as 2001/99 200199

Then select the YYYYddd radio button.

Please note that ddd or DayofYear indicates the actual numeric value of the day in the whole year. In the example, April 9th is actually the 99th day of the whole calendar year of 365 days.

Ddd

Radio button: ddd
This is DayofYear

If you determine that the actual layout of the dates given in the imported file is in the format ddd,

For example:
Date: April 9, 2001
Formatted as 99

Then select the ddd radio button.

Please note that ddd or DayofYear indicates the actual numeric value of the day in the whole year. In the example, April 9th is actually the 99th day of the whole calendar year of 365 days.

Ddmmmyy

Radio button: DDMMMYY
This is DayMonthYear

If you determine that the actual layout of the dates given in the imported file is in the format DDMMMYY,

For example:
Date: January 5, 2006
Formatted as 05JAN06

Then select the DDMMMYY radio button.

This will handle other Collect! defined formats as well and automatically convert. It will remove any date separator defined in Collect!, including the default separator, the global import date separator and the field level date separator.

This setting also detects and handles these the following three combinations automatically.

DMMMYY - 5JAN06 - Fix a 1 digit day.
DMMMYYYY - 5JAN2006 - Fix a 1 digit day and 4 digit year.
DDMMMYYYY - 05JAN2006 - Fix a 4 digit year.

The three character month value uses your operating system's "Regional Settings" to determine the value for the month. The only limitation here is it must be 3 characters in length since that is how the format above is defined. Case does not matter.

Date Separator

You must inform Collect! that the date format includes a date separator. The default value is /. Sometimes different systems use different formats.

For example:
Date: April 9, 2001
Formatted as 04/09/2001 or 04.09.2001 or 04-09-2001

Enter the date separator in this field as [ / ] or [ . ] or [ - ] as per examples above.

Day Leading Zero

(05 VS. 5)

Check this check box if the date segment in the import file has pre-appended a 0 if the day is a single figure. Do not trust the display in a spreadsheet as this often removes leading zeroes. To determine the actual layout, it is necessary to view your file in a text editor, such as Notepad.

For example:
Date: April 9, 2001
Formatted as 04/09/2001

Leave this check box blank if the date segment in the import file has NO pre-appended 0 to the day with a single digit.

For example:
Date: April 9, 2001
Formatted as 4/9/2001

Month Leading Zero

(03 VS. 3)

Check this check box if the date segment in the import file has pre-appended a 0 if the month is a single figure. Do not trust the display in a spreadsheet as this often removes leading zeroes. To determine the actual layout, it is necessary to view your file in a text editor, such as Notepad.

For example:
Date: April 9, 2001
Formatted as 04/09/2001

Leave this check box blank if the date segment in the import file has NO pre-appended 0 to the month with a single digit.

For example:
Date: April 9, 2001
Formatted as 4/9/2001

Century

(1995 VS. 95)

Check this check box if the date segment in the import file has the century in a long format. Do not trust the display in a spreadsheet as this often reformats dates as it displays them. To determine the actual layout, it is necessary to view your file in a text editor, such as Notepad.

For example:
Date: April 9, 2001
Formatted as 04/09/2001

Leave this check box blank if the date segment in the import file has the century in a short 2 digit format.

For example:
Date: April 9, 2001
Formatted as 04/09/01

No Separator

Check this check box if there is NO SEPARATOR at all in the date segment in the import file. Do not trust the display in a spreadsheet as this often reformats dates as it displays them. To determine the actual layout, it is necessary to view your file in a text editor, such as Notepad.

For example:
Date: April 9, 2001
Formatted as 04092001 040901

This will inform Collect! not to look for a separator in the date segment.

Help

Press this button for help on the Import Field Date Format dialog and links to related topics.

Cancel

Select this button to ignore any changes you may have made and then return to the previous form.

OK

Selecting this button will save any data you have entered and return you to the previous form.