Zoho Analytics: How to Construct Custom Date Format String?

---

When you import data into Zoho Analytics (from CSV, XLS, HTML etc., files) and if the data consists of date values, Zoho Analytics tries to auto identify the existence of a date column based on the values provided. If not, users could still select the date column format from the default list provided or provide a custom date format string which would match with the date column. This document  provides instructions on "How to construct custom date format string"

Reference Table - Date Format String:

The following table captures all the format strings (or abbreviations) and their meanings for your reference. Note that you could also provide any special characters (slash, comma, hypen etc.,) as part of the format strings to capture such characters that could be part of your date value.

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.	14 (means 2014), 08 (means 2008).
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 Hours in a day between 0 to 23 hrs	13:50, 20:20
hh	Indicates Hours in a day between 1 to 12 hrs (morning or afternoon is identified by AM or PM)	1:30, 11:30
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 zone abbreviation	Nov 24, 2014 10:00 pm GMT +5:30 Nov 24, 2014 10:00 pm PST
Z	Indicates Time zone based RFC 822 standard	Nov 24, 2014 +0530 Nov 24, 2014 -0247
MILLI	Indicates number of milliseconds since January 1, 1970	911899079000

Examples:

Example 1:
Date: 11/24/2014 Format String to be given: MM/dd/yyyy
11	MM	Indicates the month part
24	dd	Indicates the day part
2014	yyyy	Indicates the year part
Example 2:
Date: 11.24.2014 Format String to be given: MM.dd.yyyy
11	MM	Indicates the month part
24	dd	Indicates the day part
2014	yyyy	Indicates the year part
Example 3:
Date: Nov 24, 2014 02:47 PM Format String to be given: MMM dd, yyyy hh:mm a
Nov	MMM	Indicates the month part
24	dd	Indicates the day part
2014	yyyy	Indicates the year part
02	hh	Indicates the hour part (01-12)
47	mm	Indicates the minute part
PM	a	Indicates the AM/PM part
Example 4:
Date: Nov 24, 2014 14:47 Format String to be given: MMM dd, yyyy HH:mm
Nov	MMM	Indicates the month part
24	dd	Indicates the day part
2014	yyyy	Indicates the year part
14	HH	Indicates the hour part (00-23)
47	mm	Indicates the minute part
Example 5:
Date: November 24, 2014 Format String to be given: MMMM dd, yyyy
November	MMMM	Indicates the month part
24	dd	Indicates the day part
2014	yyyy	Indicates the year part
Example 6:
Date: Nov 24, 2014 02:47 PM GMT +5:30 Format String to be given: MMM dd, yyyy hh:mm a z
Nov	MMM	Indicates the month part
24	dd	Indicates the day part
2014	yyyy	Indicates the year part
02	hh	Indicates the hour part (01-12)
47	mm	Indicates the minute part
PM	a	Indicates the AM/PM part
GMT +5:30	z	Indicates the time zone
Example 7:
Date: Nov 24, 2014 02:47 PM PST Format String to be given: MMM dd, yyyy hh:mm a z
Nov	MMM	Indicates the month part
24	dd	Indicates the day part
2014	yyyy	Indicates the year part
02	hh	Indicates the hour part (01-12)
47	mm	Indicates the minute part
PM	a	Indicates the AM/PM part
PST	z	Indicates the time zone
Example 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 weekday part
24	dd	Indicates the day part
Nov	MMM	Indicates the month part
98	yy	Indicates the year part
14	HH	Indicates the hour part (00-23)
47	mm	Indicates the minute part
59	ss	Indicates the second part
163	SSS	Indicates the millisecond part