Data and Time Formatting Strings

 

In various places in PowerWorld, a formatting string to indicate how to convert a floating point number representing the date and time into a formatted string. For example, this is done with the Text functions in the custom expression function and operators.

 

The floating point number is assumed to represent the date and time using the following convention. The integral part of the value is the number of days that have passed since 12/30/1899. The fractional part of the value is fraction of a 24 hour day that has elapsed. This number is assumed to be stored as a double precision floating point number which results in this number being accurate to about 1 millisecond. The formatting string should then use the table below to determine the results of the Text() function.

 

The following are several examples assuming that x1 =

 

Example Function

String Result of Function

Text(42999.746, 'mm/dd/yyyy hh:nn:ss.zzz AM/PM')

09/21/2017 05:54:14.400 PM

Text(42999.746, 'd/m/yyyy hh:mm')

21/9/2017 17:54

Text(42999.746, 'h:nn ampm ''on'' ddd, m/d/yyyy')

5:54 PM on Thu, 9/21/2017

Text(42999.746, 'dddd, m/d/yyyy')

Thursday, 9/21/2017

 

In the following table, specifiers are given in lowercase. Case is ignored in formats, except for the "am/pm" and "a/p" specifiers.

 

Specifier

Displays

c

Displays the date using the format given by the short date format specified in the Windows Region and Language Settings, followed by the time using the format given by the long time format of the Windows settings. The time is not displayed if the date-time value indicates midnight precisely.

d

Displays the day as a number without a leading zero (1-31).

dd

Displays the day as a number with a leading zero (01-31).

ddd

Displays the day as an abbreviation (Sun-Sat) (The Windows Region and Language settings will impact the string used for the days)

dddd

Displays the day as a full name (Sunday-Saturday) (The Windows Region and Language settings will impact the string used for the days)

ddddd

Displays the date using the format given by the short date format specified in the Windows Region and Language settings.

dddddd

Displays the date using the format given by the long date format specified in the Windows Region and Language settings.

e

Displays the year in the current period/era as a number without a leading zero (Japanese, Korean, and Taiwanese locales only).

ee

Displays the year in the current period/era as a number with a leading zero (Japanese, Korean, and Taiwanese locales only).

g

Displays the period/era as an abbreviation (Japanese and Taiwanese locales only).

gg

Displays the period/era as a full name (Japanese and Taiwanese locales only).

m

Displays the month as a number without a leading zero (1-12). If the m specifier immediately follows an h or hh specifier, the minute rather than the month is displayed.

mm

Displays the month as a number with a leading zero (01-12). If the mm specifier immediately follows an h or hh specifier, the minute rather than the month is displayed.

mmm

Displays the month as an abbreviation (Jan-Dec) (The Windows Region and Language settings will impact the string used for the days)

mmmm

Displays the month as a full name (January-December) (The Windows Region and Language settings will impact the string used for the days)

yy

Displays the year as a two-digit number (00-99)

yyyy

Displays the year as a four-digit number (0000-9999)

h

Displays the hour without a leading zero (0-23)

hh

Displays the hour with a leading zero (00-23)

n

Displays the minute without a leading zero (0-59)

nn

Displays the minute with a leading zero (00-59)

s

Displays the second without a leading zero (0-59)

ss

Displays the second with a leading zero (00-59)

z

Displays the millisecond without a leading zero (0-999)

zzz

Displays the millisecond with a leading zero (000-999)

t

Displays the time using the format given by the short time format specified in the Windows Region and Language settings.

tt

Displays the time using the format given by the long time format specified in the Windows Region and Language settings.

am/pm

Uses the 12-hour clock for the preceding h or hh specifier, and displays 'am' for any hour before noon, and 'pm' for any hour after noon. The am/pm specifier can use lower, upper, or mixed case, and the result is displayed accordingly.

a/p

Uses the 12-hour clock for the preceding h or hh specifier, and displays 'a' for any hour before noon, and 'p' for any hour after noon. The a/p specifier can use lower, upper, or mixed case, and the result is displayed accordingly.

ampm

Uses the 12-hour clock for the preceding h or hh specifier along with either an "am" for hours before noon or "pm" for hours after noon. The Windows Region and Language settings will impact the string used for am and pm.

/

Displays the date separator character

:

Displays the time separator character

'xx'/"xx"

Characters enclosed in single or double quotation marks are displayed as such, and do not affect formatting