Edit

TextToDate (text [, format])

Result Type: Date

Definition:  Converts the given text into a value of type date. Use this if, for example, you have a textual value representing a date, and you want to perform date arithmetic on it. If you don’t explicitly convert the text value to a date value first, the date arithmetic won’t work—you will get string concatenation or an error. The format can be one of:

DateFormShort little-/middle-/big-endian order depends on locale

DateFormDDMMYY always little-endian date order

DateFormYYYYMMDD always big-endian date order

DateFormYYYYMMDDHHMMSS always big-endian date order

DateFormISO always big-endian date order

DateFormISO8601Time always big-endian date order*

If no format parameter is provided, the system's local short date format is assumed. Note: if you do not provide a format, and your expression is evaluated on a server, the local date format on the server may not be the same as the client, so results may not be what you expect. Therefore you should always provide a format parameter. There is no way to force middle-endian date order conversion other than through locale setting).

If the text consists of exactly 8 digits with no separators, the format will be assumed to be YYYYMMDD. If the text contsists of exactly 14 digits with no separators, the format will be assumed to be YYYYMMDDHHMMSS

Examples:  TextToDate("14/3/93", DateFormDDMMYY) + 2

returns 16/3/93. Compare this with "14/3/93" + 2, which gives a result of “14/3/932”.

Time Zones 

In MoneyWorks 9 and later, parsing a date using the DateFormISO8601Time format will interpret the time zone indicator and convert the resulting DateTime to the local time zone. In earlier versions, the time zone was ignored (and still is for most formats). E.g. parsing "2020-11-16T20:10:57Z" (UTC) will yield the actual DateTime 2020-11-17 09:17:50 (i.e. 2020-11-17T09:17:50+1300) if evaluated in the NZDT timezone. MoneyWorks recognises the Z (Zulu, UTC), +HHMM, and -HHMM time zone formats. Use DateFormISO8601Time to parse typical JSON timestamps (JSON_Get does not parse timestamps for you).

See Also:

CurrentPeriod: Get the current period number

Date: Convert d,m,y to a date

DateToPeriod: Convert a date to a period

DateToText: Format a date as a string

Day: Get the Day of month of a date

DayOfWeek: Get The weekday of a date

FirstUnlockedPeriod: Get the oldest period that is not locked

Month: Get the month of a date

PeriodName: Get the Name of a period

PeriodOffset: Difference between two periods

PeriodToDate: Get the end date of a period

Time: The current Datetime

TimeAdd: Add seconds to a DateTime

TimeDiff: The difference, in seconds, between two DateTimes

Timestamp: A timestamp string

Today: Today's date

WeekOfYear: Convert a date to a week number

Year: Year of a date