' ' ' ' ' '

FT4Web by Investors FastTrack

Learning about FT4Web | Blog | FLOSS | Who is FT?

star.gif (3695 bytes)

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