Constructing a custom date format
When you data import with date values, Zoho Analytics auto identifies the format of the date column based on the values being imported. You can also select the date column format from the default list or provide a custom format as required. This document provides information on constructing a custom date format string in Zoho Analytics.
Date format strings
The following table lists all the date format strings available in Zoho Analytics.
Format String | Description | Example |
yy | This string indicates the year value without century. For example, if the value is from 70 to 99 then the year is assumed as the 20th century (1970, 1971,..., 1999). Anything other than this is considered as the 21st century. | 79 (means 1979) 08 (means 2008) 14 (means 2014) 69 (means 2069) |
yyyy | This string indicates the year value with century. | 2019 |
MM | This string indicates the month value in numeric format. | 3 (means March) 11 (means November) |
MMM | This string indicates the month value as a three letter string abbreviation. | Jan |
MMMM | This string indicates the month value in full string. | January |
dd | This string indicates the day of the month. | 12 |
EEE | This string indicates the day in the week as a three letter string abbreviation. | Wed |
EEEE | This string indicates the day in the week in full string form. | Wednesday |
HH | This string indicates time in 24-Hour format. | 13:50 |
hh | This string indicates time in 12-Hour format (morning and afternoon is identified by AM and PM). | 1:30 AM 11:30 PM |
mm | This string indicates the minutes in an hour. | 1:30 |
ss | This string indicates the seconds in a minute. | 11:30:30 |
SSS | This string indicates the millisecond in the date. | 11:30:30.163 |
a | This string indicates AM or PM of the time. | 11:00 AM |
z | This string indicates the time zone based on either GMT or country specific time zones. | Nov 24, 2014 10:00 PM GMT+9:00 or Nov 24, 2014 10:00 PM JST will be saved as Nov 24, 2014 01:00 PM GMT |
Z | This string indicates the time zone based on RFC 822 standard. | Nov 24, 2018 +0530 Nov 24, 2018 -0247 |
MILLI | This string indicates the number of milliseconds since January 1, 1970. | 911899079000 |
Separators
Zoho Analytics lets you use any special characters (slash, comma, hyphen etc.,) as a separator for date. You can also use a different separator for each element. The following are a few examples of standard patterns.
- 24-11-2014 (Hyphen is used as a separator)
- 24.11.2014 (Period is used as a separator)
- Nov 24 (Space is used as a separator)
- Nov 24, 2014 (Space and comma are used as separators)
- 24/11/2014 02:47 PM (Slash, colon and space are used as separators)
Examples
The following table lists a few examples of different date format strings.
Format | Examples |
Date only | |
MM/dd | 11/24 |
dd-MM | 24-11 |
dd/MM/yy | 24/11/72 |
dd-MM-yy | 24-11-14 |
dd.MM.yy | 24.11.14 |
MM/dd/yyyy | 11/24/2014 |
MM.dd.yyyy | 11.24.2014 |
dd/MM/yyyy | 24/11/2014 |
dd.MM.yyyy | 24.11.2014 |
Date with month name | |
dd MMM | 24 Nov |
MMM-dd | Nov-24 |
MMM-dd, yyyy | Nov-24, 2014 |
MMM dd, yyyy | Nov 24, 2014 |
dd MMM, yyyy | 24 Nov, 2014 |
MMMM dd, yyyy | November 24, 2014 |
dd MMMM, yyyy | 24 November, 2014 |
Date with day | |
EEE, MMM dd yyyy | Wed, Nov 24, 2014 |
dd-MM-yyyy, EEE | 24-11-2014, Wed |
EEEE, MMM dd, yyyy | Wednesday, Nov 24, 2014 |
dd/MM/yyyy, EEEE | 24/11/2014, Wednesday |
Date with time | |
MM-dd, hh:mm:ss a | 11-24, 12:47:59 AM |
MM-dd, HH:mm:ss | 11-24, 00:47:59 |
MM/dd/yyyy hh:mm a | 11/24/2014 02:47 PM |
MM/dd/yyyy HH:mm | 11/24/2014 14:47 |
MM.dd.yyyy hh:mm a | 11.24.2014 02:47 PM |
MM.dd.yyyy HH:mm | 11.24.2014 14:47 |
Date with month name and time | |
dd MMM, yyyy hh:mm a | 24 Nov, 2014 02:47 PM |
dd MMM, yyyy HH:mm | 24 Nov, 2014 14:47 |
MMMM dd, yyyy hh:mm a | November 24, 2014 02:47 PM |
MMMM dd, yyyy HH:mm | November 24, 2014 14:47 |
EEEE, MMM dd, yyyy hh:mm a | Wednesday, Nov 24, 2014 02:47 PM |
Time zone | |
dd MMM, yyyy hh:mm a z | 24 Nov, 2014 02:47 PM GMT+05.30 |
MMMM dd, yyyy HH:mm Z | November 24, 2014 14:47 +0530 |