When setting up date formats in TotalConnect, you may need to convert textual data to dates using formulas. The PARSEDATE function can be used for the conversion. The function receives text containing the date information, and an expression that specifies how to construct the date using the text.
For example, using the expression dd/MM/yyyy along with the text 26/12/2022 would attribute the numbers to the day, month and year, in that order.
| Syntax | Meaning | Example value | Example Expression |
|---|---|---|---|
| d or dd | Day in the month | 13/09/2022 | dd/MM/yyyy |
| EEE | The abbreviated name of the day of the week e.g. Tue | Tue 26/12/2022 | EEE dd/MM/yyyy |
| EEEE | The full name of the day of the week e.g. Tuesday | Tuesday 26/12/2022 | EEEE dd/MM/yyyy |
| DD | Day in year, e.g. 45 | 45 2022 | DDD yyyy (yields 14/02/22) |
| YYYY or yyyy | Year e.g. 2017 | 13/09/2022 | dd/MM/yyyy |
| YY or yy | 2 digit year e.g. 17 | 13/09/22 | dd/MM/yy |
| MM | Number of month e.g. 10 | 13/09/2022 | dd/MM/yyyy |
| MMM | First three letters of month, e.g. oct | 13 Oct 2022 | dd MMM yyyy |
| m or mm | minutes | 13/10/2022 17:34 | dd/MM/yyyy HH:mm |
| s or ss | seconds |
13/10/2022 17:34:45 |
dd/MM/yyyy HH:mm:ss |
| S | Millisecond | 13/10/2022 17:34:45.187 | dd/MM/yyyy HH:mm:ss.SSS |
| aa | The AM/PM designator. | 13/10/2022 11PM | dd/MM/yyyy hhaa |
| h or hh | The hour, using a 12-hour clock. | 13/10/2022 11PM | dd/MM/yyyy hhaa |
| H or HH | The hour, using a 24-hour clock. | 13/10/2022 17:34 | dd/MM/yyyy HH:mm |
| z or zz or zzz | Hours offset from GMT | 13/10/2022 17:34 GMT-02:00 | dd/MM/yyyy HH:mm z (yields 13/10/2022 19:34) |
| Apostrophes ('') | Treats anything inside as letters | 2022y10m13d | yyyy'y'MM'm'dd'd' |
Extracting Information From a Date Object
Use the FORMATDATE function to extract information from a date object. The function receives a date object and an expression the specifies how the string should look.
For example, using the expression MM yyyy along with 26/12/22 extracts and displays 12 2022.
Commonly used syntax in expressions:
| Example Expression | Meaning | Result (using the date 2022/02/08 as an example) |
|---|---|---|
| yyyy | The year number | 2022 |
| yy | The year number as two digits | 22 |
| MMM | The month's short name | Feb |
| MMMM | The month's full name | February |
| MM | The month's number (two digits) | 02 |
| M | The month's number (one or two digits) | 2 |
| ww |
The number of weeks since the first week to be part of the year. The first week of 2022 starts from 27/12/22 and ends in 02/01/23 |
06 |
| d | Day in month (one or two digits) | 8 |
| dd | Day in month (always two digits) | 08 |
| DDD | Day in year (3 digits) | 039 |
| DD | Day in year (2 or 3 digits) | 39 |
| D | Day in year (1 or 2 or 3 digits) | 39 |