Analytics » Import/Upload Data into Virtutem Analytics » Virtutem Analytics - Import Date Formats

Virtutem Analytics - Import Date Formats
Virtutem Analytics: How to Construct Custom Date Format String?
When you import data into Virtutem Analytics (from CSV, XLS, HTML etc., files) and if the data consists of date values, Virtutem 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.		 98 (means 1998),
08 (means 2008).
yyyy
 Indicates Year value with century
 1998
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
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, 1998 10:00 pm GMT +5:30
Nov 24, 1998 10:00 pm PST
Z
 Indicates Time zone based RFC 822 standard
 Nov 24, 1998 +0530
Nov 24, 1998 -0247
MILLI
 Indicates number of milliseconds since January 1, 1970
 911899079000

Examples:
Example 1:
Date: 11/24/1998
Format String to be given: MM/dd/yyyy
11
 MM
 Indicates the month part
24
 dd
 Indicates the day part
1998
 yyyy
 Indicates the year part
Example 2:
Date: 11.24.1998
Format String to be given: MM.dd.yyyy
11
 MM
 Indicates the month part
24
 dd
 Indicates the day part
1998
 yyyy
 Indicates the year part
Example 3:
Date: Nov 24, 1998 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
1998
 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, 1998 14:47
Format String to be given: MMM dd, yyyy HH:mm
Nov
 MMM
 Indicates the month part
24
 dd
 Indicates the day part
1998
 yyyy
 Indicates the year part
14
 HH
 Indicates the hour part (00-23)
47
 mm
 Indicates the minute part
Example 5:
Date: November 24, 1998
Format String to be given: MMMM dd, yyyy
November
 MMMM
 Indicates the month part
24
 dd
 Indicates the day part
1998
 yyyy
 Indicates the year part
Example 6:
Date: Nov 24, 1998 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
1998
 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, 1998 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
1998
 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
Format String to be given: EEEE, dd-MMM-yy HH:mm:ss
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