I've been referencing the MSDN articles a lot recently on Date and Time format strings and sometimes I get confused by the many options and sometimes these formats do not carry over or function. So I wanted to build a quick reference sheet that I can refer to in my workflows and forms.
First, how to use the inline and runtime functions.
fn-FormatDate in Workflows
Represents a date time value in text of a specific format.
Usage
fn-FormatDate({WorkflowVariable:MyDate}, d)
Other Notes
- For workflow inline functions, using single quotes takes the value as a literal. So for the formateDate function fn-FormatDate({WorkflowVariable:MyDate}, 'D') provides the value of D.
- Using double quotes or no quotes produces the same result. For example:
fn-FormatDate({WorkflowVariable:MyDate}, "dd/MM/yyyy") and fn-FormatDate({WorkflowVariable:MyDate}, dd/MM/yyyy) both produce 11/08/2015
fn-FormatDate({WorkflowVariable:MyDate}, "D") and fn-FormatDate({WorkflowVariable:MyDate}, D) both produce Tuesday, August 11, 2015
- If you attempt to calculate for a time format, but the value provided is date ONLY, then the function will result in an error.
Time Calculations
For Time intervals, I found that using a single h, to provide the non-leading zero hour does not work and results in an error. But if you use the special format character % you can get the result of a non-leading zero hour digit. For exmaple: fn-FormatDate({WorkflowVariable:MyDate}, %h) returns 8, while fn-FormatDate({WorkflowVariable:MyDate}, hh) returns 08.
formatDate on Forms
Returns the date in the specified format.
Usage
formatDate(date, format) or formateDate({namedControl}, "D")
Other Notes
- In the format string, using single or double quotes is required. Using no quotes results in an error.
Table of Date and Time Format Strings
The specified format can incorporate structures such as "dd/MM/yyyy" or "dddd", "dd MMM". Alternatively, single character format specifiers can be used. The components of these structures include:
Element Description Example (in US date format)
Format Specifier | Description | Examples | Result |
"dd" | Day of month (double digit) 3/1/2017 | fn-FormatDate({CurrentDate}, "dd") | 01 |
"ddd" | Day of week (Abbreviated) 3/1/2017 | fn-FormatDate({CurrentDate}, "ddd") | Wed or W (see notes) |
"dddd" | Day of week (Full) 3/1/2017 | fn-FormatDate({CurrentDate}, "dddd") | Wednesday |
"MM" | Month (Numeric) 3/1/2017 | fn-FormatDate({CurrentDate}, "MM") | 03 |
"MMM" | Month (Short) 3/1/2017 | fn-FormatDate({CurrentDate}, "MMM") | Mar |
"MMMM" | Month (Full) 3/1/2017 | fn-FormatDate({CurrentDate}, "MMMM") | March |
"yyyy" | Year 3/1/2017 1:45:30 PM | fn-FormatDate({CurrentDate}, "yyyy") | 2017 |
"%h" | Hours (12 Hour) 3/1/2017 1:45:30 PM | fn-FormatDate({CurrentDate}, "%h") | 1 |
"hh" | Hours (12 Hour) 3/1/2017 1:45:30 PM | fn-FormatDate({CurrentDate}, "hh") | 01 in WF, but 1 in Forms |
"HH" | Hours (24 hour) 3/1/2017 1:45:30 PM | fn-FormatDate({CurrentDate}, "HH") | 13 |
"mm" | Minutes 3/1/2017 1:45:30 PM | fn-FormatDate({CurrentDate}, "mm") | 45 |
"tt" | AM or PM 3/1/2017 1:45:30 PM | fn-FormatDate({CurrentDate}, "tt") | PM |
"d" | Short date pattern 3/1/2017 | fn-FormatDate({CurrentDate}, "d") | 3/1/2017 |
"D" | Long date pattern 3/1/2017 | fn-FormatDate({CurrentDate}, "D") | Wednesday, March 1, 2017 |
"M", "m" | Month/day pattern 3/1/2017 | fn-FormatDate({CurrentDate}, "M") | March 1 |
"s" | Sortable date/time pattern 6/15/2009 1:45:30 PM | fn-FormatDate({CurrentDate}, "s") | 2009-06-15T13:45:30 |
"t" | Short time 3/1/2017 1:45:30 PM | fn-FormatDate({CurrentDate}, "t") | 1:45PM |
"T" | Long time 3/1/2017 1:45:30 PM | fn-FormatDate({CurrentDate}, "T") | 1:45:30PM |
"Y", "y" | Year month 3/1/2017 1:45:30 PM | fn-FormatDate({CurrentDate}, "Y") | March 2017 |
"M/d/y" | Short Date pattern 3/1/2017 | fn-FormatDate({CurrentDate}, "M/d/y") | 3/1/17 |
"d/M/y" | Short Date pattern 3/1/2017 | fn-FormatDate({CurrentDate}, "d/M/y") | 1/3/17 |
"MM/dd/yyyy" | Short Date with leading zero Day and Month | fn-FormatDate({CurrentDate}, "MM/dd/yyyy") | 03/01/2017 |
"M/dd/yyyy" | Short Date with leading zero Day | fn-FormatDate({CurrentDate}, "M/dd/yyyy") | 3/01/2017 |
"MM/d/yyyy" | Short Date with leading zero Month | fn-FormatDate({CurrentDate}, "MM/d/yyyy") | 03/1/2017 |
NOTES:
- dd - will provide a two digit days, so the first of the month will be 01
- MM - will provide a two digit month, so March will be seen as 03. Using a single M will be just 3, a single digit. Notice that capital M is month and lowercase m is minutes.
- d - can be used in two different ways. "d" by itself provides a short date pattern 3/1/2017, while using d in a date pattern like "M/d/y" will provide 3/1/17. Notice it is a single digit day when appropriate.
- y - can be used in two different ways. "y" by itself provides a Year Month pattern of March 2017. But using "y" in a date pattern like "M/d/y" will provide 3/1/17. Notice the year is only the last two digits.
- Using "ddd" provides an abbreviation of the day name. But it was found to react differently based on platform and local
- On Prem: return W in English, other locals produce a 2 or 3 character response as appropriate. As Marian Hatala noted, the value of day name abbrv is taken from Sys.CultureInfo.CurrentCulture.dateTimeFormat.ShortestDayNames
- O365: returns Wed
