DateFormat function

Syntax

DateFormat(date_expression, format_string [, time_zone [, from_format]])

Parameters

date_expression*

The date value to be formatted. Can be a Date or a String. Note: This parameter accepts an expression, meaning you can provide a literal value, a column reference, or the result of another function. Required

This argument can take a column of the Date or Label type. It can also take the result of a nested formula or a String constant.

A date value in UNIX Time Stamp (epoch time) format (this can be generated using the Now( ) function), or any of the supported Apptiodate formats. Unix Epoch time is the number of seconds that have elapsed since January 1, 1970.

NOTICE

Elements of date values can be separated by a dash ( - ) or a slash ( / ). Therefore, M-d-yy is the same as M/d/yy.

"ffff" is a fiscal year definition and "yyyy" is a calendar year definition. If your fiscal year starts in a month other than January, these may not give the same value.

‘p’ and ‘M’ are different in that ‘M’ is the name of the calendar month for the period. Apptio supports 13-period calendars, where the time periods may not align to calendar months. In a 13-period calendar, ‘P’ will return the period number (1 for the 1st period of the fiscal year) since month names cannot uniquely identify every period.

Table 1.
Format	Example output
ppp	Jan
pppp	January
ffff	FY2012
yy	09
yyyy	2009
MMM	Jan
MMM-yy	Jan-09
M-d-yy	1-20-09
M-d-yy HH:mm	1-20-09 14:00
M-d-yy hh:mm a	1-20-09 2:00 PM
M-d-yy HH:mm:ss	1-20-09 14:00:30
M-d-yy hh:mm:ss a	1-20-09 2:00:30 PM
M-d-yy HH:mm:ss.S	1-20-09 14:00:30.007
M-d-yy hh:mm:ss.S a	1-20-09 2:00:30.007 PM
yyyy-M-d	2009-1-20
yyyy-M-d HH:mm	2009-1-20 14:00
yyyy-M-d hh:mm a	2009-1-20 2:00 PM
yyyy-M-d HH:mm:ss	2009-1-20 14:00:30
yyyy-M-d hh:mm:ss a	2009-1-20 2:00:30 PM
yyyy-M-d HH:mm:ss.S	2009-1-20 14:00:30.007
yyyy-M-d hh:mm:ss.S a	2009-1-20 2:00:30.007 PM
MMM d, yyyy	Jan 20, 2009
dd-MMM	20-Jan
dd-MMM-yyyy	20-Jan-2009
dd-MMM-yyyy HH:mm	20-Jan-2009 14:00
dd-MMM-yyyy hh:mm a	20-Jan-2009 2:00 PM
dd-MMM-yyyy HH:mm:ss	20-Jan-2009 14:00:30
dd-MMM-yyyy hh:mm:ss a	20-Jan-2009 2:00:30 PM
dd-MMM-yyyy HH:mm:ss.S	20-Jan-2009 14:00:30.007
dd-MMM-yyyy hh:mm:ss.S a	20-Jan-2009 2:00:30.007 PM
dd-MM-yyyy	20-01-2009
dd-MM-yyyy HH:mm	20-01-2009 14:00
dd-MM-yyyy hh:mm a	20-01-2009 2:00 PM
dd-MM-yyyy HH:mm:ss	20-01-2009 14:00:30
dd-MM-yyyy h:mm:ss a	20-01-2009 2:00:30 PM
dd-MM-yyyy HH:mm:ss.S	20-01-2009 14:00:30.007
dd-MM-yyyy hh:mm:ss.S a	20-01-2009 2:00:30.007 PM
Eon yy	Eon 09

format_string*

Specifies the desired format of the output string. Uses standard date formatting patterns (e.g., 'yyyy-MM-dd'). Note: This parameter accepts an expression, meaning you can provide a literal value, a column reference, or the result of another function. Required

Specifies the format for the string returned by the function. This argument can take a column of type Label. It can also take the result of a nested formula that returns a label, or a String constant.

Table 2.
Format Element	Format	Description	Example
M	M MM MMM MMMMM	Month	M: 6 MM: 06 MMM: Jun MMMM: June
D	D DD DDD	Day in the year. The number of Ds determines the number of digits displayed	D: 7 (January 7th) DD: 07 (January 7th) DDD: 007 (January 7th)
d	d dd	Day in month (1 through 31)	d: 2 dd: 02
w	w ww	The week of the year (1 through 52)	w: 9 ww: 09
W	W	Week of the month in which the date occurs (1 through 5)	1
E	E	Day in the week	Mon
E	EEEE	Day in the week (long)	Monday
F	F	Day of week in the month (the number of times the weekday has occurred during the month)	2
y	y yy	Year	y: 14 yyyy: 2014
H	H HH	Hour in military time (0-23)	H: 4, 14 HH: 04, 14
h	h hh	Hour in am/pm (1-12)	h: 2 hh: 02
m	m mm	Minute (0-59)	m: 3, 30 mm: 03, 30
s	s ss	Second in minute (0-59)	s: 3, 30 ss: 03, 30
S	S SS SSS	Millisecond	S: 7 SS: 07 SSS: 007
a		Display AM or PM as appropriate	AM
z	z	General time zone (short format)	GMT
z	zzzz	General time zone (long format)	Greenwich Mean Time
Z	Z	RFC822 time zone (offset from GMT)	8 (for Pacific time)
p	ppp	Short period description	Period calendar types: P1 Gregorian calendar type: Mar
p	pppp	Long period description	Period calendar types: Period 1 Gregorian calendar type: March
f	ffff	Period fiscal year	FY2014

time_zone

Specifies the time zone for displaying the date and time. To include the time zone in the output, the format_string must include a 'z'. Optional (default: GMT)

from_format

Specifies the current format of the date_expression if it is a String. Used to correctly parse the date before formatting. Note: This parameter accepts an expression, meaning you can provide a literal value, a column reference, or the result of another function. Optional (default: Locale-dependent default parsing)

Special characters and escaping

The following special characters can be used in a quoted date string:

Period (.)
Forward Slash (/)
Hyphen (-)

To add additional text to the date format-string, enclose the text in single quotes. For example, for the date and time value Jan. 12, 2015 12:15 am:

MMM dd, yyyy 'at' hh:mm a

time_zone

Indicates the time zone used to display the time. For a time zone to be displayed in a date, use a format string that includes a "z" argument at the end. If this argument is not included, the time will be displayed in GMT format. All time zones included in the Java TimeZone class can be used in this argument. To see a complete list of time zones, search the web for "Java TimeZone class." If you want to specify arguments after this, but not specify a time zone, you can specify "" for this argument as follows:

=Dateformat(Column1,"MM/dd/yy","",MMM dd,yyyy")

from_format

[Supported on 12.4.1+]

Indicates the current format of the date_expression. This format is used to parse the date. If unspecified, the system will try to parse the date with the default dates for the current locale. If the user wants to specify a from_format, but with the time zone used to display the time. For a time zone to be displayed in a date, use a format string that includes a "z" argument at the end. If this argument is not included, the time will be displayed in GMT format. All time zones included in the Java TimeZone class can be used in this argument. To see a complete list of time zones, search the web for "Java TimeZone class."

The syntax for this argument is the same as that of the date_format argument.

Examples

The following examples assume a date of Oct. 12, 2014 10:24:52 AM.

Formats the current date as 'year-month-day'.
```
DateFormat(CurrentDate(), "yyyy-MM-dd")
```
Parses the input ISO timestamp, shifts to Pacific Time, and formats as 'Apr 29, 2025'.
```
DateFormat("2025-04-29T08:00:00", "MMM dd, yyyy", "PST", "yyyy-MM-dd'T'HH:mm:ss")
```

Note: When using time_zone, the format_string must include 'z' to actually show the time zone in the result. If from_format is omitted and date_expression is a string, parsing may fail if the format doesn't match the system's default assumptions.

Return Type

String