Updated 01/31/14

Date Format for Exporting SpreadSheet, Fixed length and Comma Delimited Files

This format are used on the Export tab. You key them in free-format. Use MM/DD/YY if unsure.

The following are examples of user-defined date and time formats for December 7, 1958:

Format

Display

 

mm/d/yy	12/7/58
mm/dd/yyyy	12/07/1958
\"mm-dd-yy\"	"12/07/58"
d mmmm	7 December
mmmm yy	December 58
d-mmm	7-Dec
d-mmmm-yy	7-December-58
dddddd	Sunday, December 7, 1958
j	21536

Character	Description
/	Date separator. In some locales, other characters may be used to represent the date separator. The date separator separates the day, month, and year when date values are formatted. The actual character used as the date separator in formatted output is determined by your system settings.
d	Display the day as a number without a leading zero (1 – 31).
dd	Display the day as a number with a leading zero (01 – 31)
ddd    .	Display the day as an abbreviation (Sun – Sat)
dddd	Display the day as a full name (Sunday – Saturday).
ddddd	Display the date as a complete date (including day, month, and year), formatted according to your system's short date format setting. For Microsoft Windows, the default short date format is m/d/yy.
dddddd	Display the date as a complete date (including day of the week, month, day, and year) formatted according to your system's long date format setting.  For Microsoft Windows, the default long date format is  mmmm dd, yyyy
w	Display the day of the week as a number (1 for Sunday through 7 for Saturday).
ww	Display the week of the year as a number (1 – 54).
m	Display the month as a number without a leading zero (1 – 12).
mm	Display the month as a number with a leading zero (01 – 12).
mmm	Display the month as an abbreviation (Jan – Dec)
mmmm	Display the month as a full month name (January – December)
q	Display the quarter of the year as a number (1 – 4).
y	Display the day of the year as a number (1 – 366).
yy	Display the year as a 2-digit number (00 – 99)
yyyy	Display the year as a 4-digit number (100 – 9999).
j	Julian date: The number of days since 12/31/1899. This is the standard calculation used for most programming languages and spreadsheets that recognize Julian dates. This character must be used alone . . . a single J as the date format.

You may insert other characters in the date format

Characters preceded by a backslash ( \ ) are output along with the date unchanged. You might use this to craft custom formulae. For example, a date format exported to Excel:

\=\D\A\T\E\V\A\L\U\E\(\"MM/DD/YY\"\)\+\1\0

Creates a column of Julian dates and add 10 days to each date. The Excel formula is,

=DATEVALUE("MM/DD/YY")+10

---