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 StringDescriptionExample
yyThis 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)
yyyyThis string indicates the year value with century.2019
MMThis string indicates the month value in numeric format.3 (means March)
11 (means November)
MMMThis string indicates the month value as a three letter string abbreviation.Jan
MMMMThis string indicates the month value in full string.January
ddThis string indicates the day of the month.12
EEEThis string indicates the day in the week as a three letter string abbreviation.Wed
EEEEThis string indicates the day in the week in full string form.Wednesday
HHThis string indicates time in 24-Hour format.13:50
hhThis string indicates time in 12-Hour format (morning and afternoon is identified by AM and PM).1:30 AM
11:30 PM
mmThis string indicates the minutes in an hour.1:30
ssThis string indicates the seconds in a minute.11:30:30
SSSThis string indicates the millisecond in the date.11:30:30.163
aThis string indicates AM or PM of the time.11:00 AM
zThis 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
ZThis string indicates the time zone based on RFC 822 standard.Nov 24, 2018 +0530
Nov 24, 2018 -0247
MILLIThis 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.

FormatExamples
Date only
MM/dd11/24
dd-MM24-11
dd/MM/yy24/11/72
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