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
yyIndicates 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).
yyyyIndicates Year value with century2014
MMIndicates Month value in numeric3 (means March),
11 (means November)
MMMIndicates Month value as a 3 letter string abbreviation (Jan, Feb)Jan, Feb
MMMMIndicates Month value in full stringJanuary, February
ddIndicates Day in the month12, 31
EEEIndicates Day in the week (abbreviation)Wed, Sun
EEEEIndicates Day in the week (full string form)Wednesday, Sunday
HHIndicates Time in 24-Hour format (0 to 23 hrs)13:50, 20:20
hhIndicates Time in 12-Hour format (0 to 12 hrs)
(morning or afternoon is identified by AM or PM)		1:30 AM, 11:30 PM
mmIndicates Minutes in the hour1:30
ssIndicates Seconds in the minute11:30: 30
SSSIndicates the Millisecond in the Date11:30:30. 163
aIndicates AM/PM of the time11:00 AM, 2:00 PM
zIndicates 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
MILLIIndicates number of milliseconds since January 1, 1970911899079000

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.

FormatExamples
Date Only
MM/dd11/24
dd-MM24-11
dd/MM/yy24/11/72  (72 means 1972. Click here to learn more.)
dd-MM-yy24-11-14
dd.MM.yy24.11.14
MM/dd/yyyy11/24/2014
MM.dd.yyyy11.24.2014
dd/MM/yyyy24/11/2014
dd.MM.yyyy24.11.2014
Date with Month Name
dd MMM24 Nov
MMM-ddNov-24
MMM-dd, yyyyNov-24, 2014
MMM dd, yyyyNov 24, 2014
dd MMM, yyyy24 Nov, 2014
MMMM dd, yyyyNovember 24, 2014
dd MMMM, yyyy24 November, 2014
Date with Day
EEE, MMM dd yyyyWed, Nov 24, 2014
dd-MM-yyyy, EEE24-11-2014, Wed
EEEE, MMM dd, yyyyWednesday, Nov 24, 2014
dd/MM/yyyy, EEEE24/11/2014, Wednesday
Date with Time
MM-dd, hh:mm:ss a11-24, 12:47:59 AM
MM-dd, HH:mm:ss11-24, 00:47:59
MM/dd/yyyy hh:mm a11/24/2014 02:47 PM
MM/dd/yyyy HH:mm11/24/2014 14:47
MM.dd.yyyy hh:mm a11.24.2014 02:47 PM
MM.dd.yyyy HH:mm11.24.2014 14:47
Date with Month Name and Time
dd MMM, yyyy hh:mm a24 Nov, 2014 02:47 PM
dd MMM, yyyy HH:mm24 Nov, 2014 14:47
MMMM dd, yyyy hh:mm aNovember 24, 2014 02:47 PM
MMMM dd, yyyy HH:mmNovember 24, 2014 14:47
EEEE, MMM dd, yyyy hh:mm aWednesday, Nov 24, 2014 02:47 PM
Time Zone
dd MMM, yyyy hh:mm a z24 Nov, 2014 02:47 PM GMT+05.30
MMMM dd, yyyy HH:mm ZNovember 24, 2014 14:47 +0530
To know about the working of time zone format strings, click here.