Constructing a Custom Date Format String
When you import data into Analytics Plus (from CSV, XLS, HTML etc., files) and if the data contains date values, Analytics Plus tries to auto identify the existence of a date column based on the values provided. Users can also select the required date column format from the default list provided, or provide a custom date format string to match with the date column being imported. This section provides instructions on constructing a custom date format string.
Reference Table
The following table captures all the date format strings (or abbreviations) and their meanings. Special characters (slash, comma, etc.) can also be included as part of the format strings.
Format String | Meaning | Example |
| yy | Indicates Year value without century. If the value is from 70 to 99, year is assumed as 20th century (1970, 1971... 1999). Anything other than this is considered as the 21st century. | 14 (means 2019), 08 (means 2008). |
| yyyy | Indicates Year value with century. | 2019 |
| MM | Indicates Month value in numeric format. | 3 (means March), 11 (means November). |
| MMM | Indicates Month value as a 3 letter string abbreviation. | Jan, Feb |
| MMMM | Indicates Month value in full string. | January, February |
| dd | Indicates the Day of the month. | 12, 30 |
| EEE | Indicates the Day in the week (abbreviation). | Wed, Sun |
| EEEE | Indicates Day in the week (full string form). | Wednesday, Sunday |
| HH | Indicates Hours in a day, between 0 and 23 hrs. | 13:50, 20:20 |
| hh | Indicates Hours in a day, between 1 to 12 hrs (morning and afternoon is identified by AM and PM). | 1:30, 11:30 |
| mm | Indicates the Minutes in an hour. | 1:30 |
| ss | Indicates the Seconds in a minute | 11:30:30 |
| SSS | Indicates the Millisecond in the Date. | 11:30:30.163 |
| a | Indicates AM/PM of the time | 11:00 am, 2:00 pm |
| z | Indicates the Time zone based on either GMT or country specific time zone abbreviation. | Nov 24, 2018 10:00 pm GMT +5:30 Nov 24, 2018 10:00 pm PST |
| Z | Indicates the Time zone based on RFC 822 standard. | Nov 24, 2018 +0530 Nov 24, 2018 -0247 |
| MILLI | Indicates number of milliseconds since January 1, 1970. | 911899079000 |
Examples
| 1 | Date: 11/24/2018 Format String to be given: MM/dd/yyyy | ||
| 11 | MM | Indicates the month. | |
| 24 | dd | Indicates the day. | |
| 2018 | yyy | Indicates the year. | |
| 2 | Date: 11.24.2018 Format String to be given: MM.dd.yyyy | ||
| 11 | MM | Indicates the month. | |
| 24 | dd | Indicates the day. | |
| 2018 | yyy | Indicates the year. | |
| 3 | Date: Nov 24, 2018 02:47 PM Format String to be given: MMM dd, yyyy hh:mm a | ||
| Nov | MMM | Indicates the month. | |
| 24 | dd | Indicates the day. | |
| 2018 | yyy | Indicates the year. | |
| 02 | hh | Indicates the hour (01-12). | |
| 47 | mm | Indicates the minute. | |
| PM | a | Indicates the AM/PM. | |
| 4 | Date: Nov 24, 2018 14:47 Format String to be given: MMM dd, yyyy HH:mm | ||
| Nov | MMM | Indicates the month. | |
| 24 | dd | Indicates the day. | |
| 2018 | yyy | Indicates the year. | |
| 02 | hh | Indicates the hour (01-12). | |
| 47 | mm | Indicates the minute. | |
| PM | a | Indicates the AM/PM. | |
| 5 | Date: 11/24/2018 Format String to be given: MM/dd/yyyy | ||
| 11 | MM | Indicates the month. | |
| 24 | dd | Indicates the day. | |
| 2018 | yyy | Indicates the year. | |
| 6 | Date: Nov 24, 2018 02:47 PM GMT +5:30 Format String to be given: MMM dd, yyyy hh:mm a z | ||
| Nov | MMM | Indicates the month. | |
| 24 | dd | Indicates the day. | |
| 2018 | yyy | Indicates the year. | |
| 02 | hh | Indicates the hour (01-12). | |
| 47 | mm | Indicates the minute. | |
| PM | a | Indicates the AM/PM. | |
| GMT +5:30 | z | Indicates the time zone. | |
| 7 | Date: Nov 24, 2018 02:47 PM PST Format String to be given: MMM dd, yyyy hh:mm a z | ||
| Nov | MMM | Indicates the month. | |
| 24 | dd | Indicates the day. | |
| 2018 | yyy | Indicates the year. | |
| 02 | hh | Indicates the hour (01-12). | |
| 47 | mm | Indicates the minute. | |
| PM | a | Indicates the AM/PM. | |
| PST | z | Indicates the time zone. | |
| 8 | Date: Wednesday, 24-Nov-98 14:47:59.163 Format String to be given: EEEE, dd-MMM-yy HH:mm:ss.SSS | ||
| Wednesday | EEEE | Indicates the weekday. | |
| 24 | dd | Indicates the day. | |
| Nov | MMM | Indicates the month. | |
| 98 | yy | Indicates the year. | |
| 14 | HH | Indicates the hour (00-23). | |
| 47 | mm | Indicates the minute. | |
| 69 | ss | Indicates the second. | |
| 163 | SSS | Indicates the millisecond. | |