Custom Date Format
When you import data into Zoho Analytics (from CSV, XLS, HTML etc., files) and if the data consists of date values, Zoho Analytics auto identifies the date column format based on the values provided. If there is any difference in the auto-identified date column format, the user could change it from the default list provided, or create a custom date format string that would match the date column. This document provides instructions on constructing a custom date format string.
Date Format Strings
The following table lists the supported strings to construct the date format.
| Format String | Meaning | Example |
| yy | Indicates Year value without century. If the value is from 70 to 99, year is assumed in 20th century (1970, 1971... 1999). Anything other than this is taken to be in the 21st century. | 79 (means 1979), 08 (means 2008), 14 (means 2014), 69 (means 2069). |
| yyyy | Indicates Year value with century | 2014 |
| MM | Indicates Month value in numeric | 3 (means March), 11 (means November) |
| MMM | Indicates Month value as a 3 letter string abbreviation (Jan, Feb) | Jan, Feb |
| MMMM | Indicates Month value in full string | January, February |
| dd | Indicates Day in the month | 12, 31 |
| EEE | Indicates Day in the week (abbreviation) | Wed, Sun |
| EEEE | Indicates Day in the week (full string form) | Wednesday, Sunday |
| HH | Indicates Time in 24-Hour format (0 to 23 hrs) | 13:50, 20:20 |
| hh | Indicates Time in 12-Hour format (0 to 12 hrs) (morning or afternoon is identified by AM or PM) | 1:30 AM, 11:30 PM |
| mm | Indicates Minutes in the hour | 1:30 |
| ss | Indicates Seconds in the 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 time zone based on either GMT or country specific time zones Note: On importing, the difference in time zone with respect to GMT will be altered accordingly and stored as GMT+00:00. To convert it to your time zone, use the Convert Time Zone function. | Nov 24, 2014 10:00 PM GMT+9:00 or Nov 24, 2014 10:00 PM JST will be saved in Zoho Analytics as Nov 24, 2014 01:00 PM GMT |
| Z | Indicates Time zone based RFC 822 standard To learn more about the working of this format, refer to the note in the above section. | Nov 24, 2014 +0900 Nov 24, 2014 -0247 |
| MILLI | Indicates number of milliseconds since January 1, 1970 | 911899079000 |
Separators
You can 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
- 24.11.2014
- 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 provides 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 (72 means 1972. Click here to learn more.) |
| 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 |
| To know about the working of time zone format strings, click here. | |