Date and time formats supported in Zoho DataPrep

Date and time formats supported in Zoho DataPrep


Datetime

A datetime format is formed using various format strings such as yyyy, dd, mm, etc. All the supported format strings are incorporated into the date picker feature of DataPrep. You can simply click to select the required element to form or customize a date or a datetime format instead of manually writing the format string yourselves. In that sense, the date picker is a collection of format strings of datetime components in the form of tiles to choose from. Date picker is available in the following transforms in Zoho DataPrep.
  1. Change data type
  2. Change date format
  3. Unify date formats 
You can click the calendar icon (which appears while hovering your mouse over the date format input field) in the above transforms to access the date picker and create your own format. Here's a snapshot below.



While choosing the required format from the date picker makes it all the more easier, it could be helpful to understand them as format strings as well. 

Time

A time format is formed using various format strings such as HH, hh, mm, ss, etc., and all these format strings are incorporated into the time picker feature. You can simply click the required elements and customize a time format. A time picker is a collection of format strings of time components in the form of tiles to choose from. Time picker is available in the Change time format transform.



Zoho DataPrep supports the datetime and time format strings listed in the table below.

 Format String
 Description
 Example
yy
Indicates Year without century. If the value is from 70 to 99, the year is taken  in the 20th century (1970, 1971... 1999). 
14 (2014)
08 (2008)
yyyy
Indicates Year with century
2014
MM
Indicates Month in numeric
3 (March)
MMM
Indicates Month as a three letter text abbreviation (Jan, Feb, etc.)
Jan
MMMM
Indicates Month in full text
January
dd
Indicates Day in the month
12
EEE
Indicates Day in the week (abbreviation)
Wed
EEEE
Indicates Day in the week (full form)
Wednesday
HH
Indicates Hours in a day between 0 to 23 hrs
23:50
hh
Indicates Hours in a day between 1 to 12 hrs (morning or afternoon is identified by AM or PM)
10:30
mm
Indicates Minutes in the hour from 01 to 12
1:30
m
Indicates Minutes in the hour from 1 to 12
1:5
ss
Indicates Seconds in the minute from 00 to 59
11:30:45
s
Indicates Seconds in the minute from 0 to 59
11:30:5
SSS
Indicates the Millisecond in the Date or Time
11:30:45:163
a
Indicates AM/PM of the time
11:00 AM, 2:00 PM
ZZ
Indicates the time zone offset with colon  separator
Nov 24, 2014 10:00 PM +5:30
Z
Indicates the time zone offset without the colon separator
Nov 24, 2014 10:00 PM +0530
millis
Indicates number of milliseconds since January 1, 1970. This format is applicable for timestamp data type.
1635509077

Delimiters

A delimiter is a separator, usually a symbol or a whitespace that separates one date or time element from the other. 

For example,  in the date: 11/24/2020 , / is the delimiter that separates the date elements. Zoho DataPrep supports the delimiters given in the table below.

Category
Delimiters
Date time delimiters
/ - . ,    : _T \s
AM/PM delimiters
\s
Time zone delimiters
- Z
Time delimiters
/ : - , . \s 

Notes
Note: \s denotes single whitespace. Please provide an actual space when you provide the input. 

Supported date formats

Here are a few examples of the supported date formats.

Example 1
Date: 11/24/2020
Format string: MM/dd/yyyy
11 MM Indicates the month
24 dd Indicates the day
2020 yyyy Indicates the year

Example 2
Date: 11.24.2020
Format string: MM.dd.yyyy
11 MM Indicates the month
24 dd Indicates the day
2020 yyyy Indicates the year

Example 3
Date: 2020/11/24
Format string: yyyy/MM/dd
2020
yyyy
Indicates the year
11
MM
Indicates the month
24
dd
Indicates the day

Example 4
Date: November 24, 2020
Format string: MMMM dd, yyyy
November
MMMM
Indicates the month
24
dd
Indicates the day
2020
yyyy
Indicates the year

Supported datetime formats

Here are a few examples of the supported datetime formats.

Example 1
Date: 11/24/2020
Format string: MM/dd/yyyy
11
MM
Indicates the month
24
dd
Indicates the day
2020
yyyy
Indicates the year

Example 2
Date: 11.24.2020
Format string: MM.dd.yyyy
11 MM Indicates the month
24 dd Indicates the day
2020 yyyy Indicates the year

Example 3
Date: Nov 24, 2020 02:47 PM
Format string: MMM dd, yyyy hh:mm a
Nov MMM Indicates the month
24 dd Indicates the day
2020 yyyy Indicates the year
02
hh
Indicates the hour (0-12)
47
mm
Indicates the minute
PM
a
AM/PM

Example 4
Format string: MMM dd, yyyy HH:mm
Date: Nov 24, 2020 14:47
Nov MMM Indicates the month
24 dd Indicates the day
2020 yyyy Indicates the year
14 HH Indicates the hour(00-23)
47 mm Indicates the minute

Example 5
Date: November 24, 2020
Format String: MMMM dd, yyyy
November
MMMM
Indicates the month
24
dd
Indicates the day
2020
yyyy
Indicates the year

Example 6
Date:Nov 24, 2020 02:47 PM +5:30
Format String to be given: MMM dd, yyyy hh:mm a ZZ
Nov
MMM
Indicates the month
24
dd
Indicates the day
2020
yyyy
Indicates the year
02
hh
Indicates the hour (01-12)
47
mm
Indicates the minute
PM
a
AM/PM
+5.30
ZZ
Indicates the time zone

Example 7
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 the weekday
24
dd
Indicates the day
Nov
MMM
Indicates the month
98
yy
Indicates the year
14
HH
Indicates the hour (00-23)
47
mm
Indicates the minutes
59
ss
Indicates the seconds
163
SSS
Indicates the milliseconds

Invalid date and datetime formats

A date or date-time format is invalid if one or more of the following conditions are not met:
  1. Any date format missing one or more of the Day, Month, and Year format strings is considered an invalid date format.
    1. For example, in the date, 25-2021, month is missing which renders it as an invalid date format.

  2. You can use the characters, 'Z' and 'T' only once in a date-time format.
    1. Examples: 2021-02-28T12:21:33T+0530, 2021-02-28T12:21:33+0530-03:30

  3. The format that follows the character, 'T' must be related to time such as Hours, Minutes, Seconds, etc.
    1. For example, in the date-time format, 28-08-T2021 12:21:33AM, T is followed by the Year component which renders it as an invalid date-time format.

  4. A date-time format must contain one of the Hours format.
    1. For example, in the date-time format, 12-05-2021 :47:59 PM, the hours renders it as an invalid date-time format.

  5. In a date-time format, you can use either the character, 'Z ' or 'ZZ' format strings.
    1. For example, in the date, Nov 24, 2020 10:00PM+0530+05:30, both 'Z' and 'ZZ' are used which makes it an invalid date-time format.

  6. You can have only one format from the Day, Month, Year, Hours, Minutes, and Seconds.
    1. For example, 01-04-2021 02:47:59PM is a valid date-time format with one format string for each date-time component.

Supported Time formats

Example 1
Time: 21:21:01
Format string: HH:mm:ss
21
HH
Indicates the hours(00-23)
21
mm
Indicates the minutes
01
ss
Indicates the seconds

Example 2
Time: 11:21
Format string: HH:mm 
11
HH
Indicates the hour(00-23)
21
mm
Indicates the minutes

Example 3
Time: 21:21:01.999
Format string: HH:mm:ss:SSS 
21
HH
Indicates the hour(00-23)
21
mm
Indicates the minutes
01
ss
Indicates the seconds
999
SSS
Indicates the milliseconds


Example 4
Time: 11:21:01.999 AM
Format string: hh:mm:ss.SSS a 
11
hh
Indicates the hour(01-12)
21
mm
Indicates the minutes
01
ss
Indicates the seconds
999
SSS
Indicates the milliseconds
AM
a
AM/PM

Example 5
Time: 11:21:09 PM
Format string: hh:mm:ss a 
11
hh
Indicates the hour(01-12)
21
mm
Indicates the minutes
09
ss
Indicates the seconds
PM
a
AM/PM

Example 6
Time: 12:21 PM
Format string: hh:mm a 
12
hh
Indicates the hour(01-12)
21
mm
Indicates the minutes
PM
a
AM/PM

Example 7
Time: 11 PM
Format string: hh a
11
hh
Indicates the hour(01-12)
PM
a
AM/PM

Invalid time formats

A time format is invalid if one or more of the following conditions are not met:
  1. A time format missing an Hours tile.
    1. For example, in the time format,  :47:59 PM, the hours renders it as an invalid date-time format.
  2. In a time format, you can use any one of the T.zone tiles.
    1. For example, 12:21:33+0530+05:30, both T.zones are used which makes it an invalid time format.
  3. You can choose only one format from the Hours, Mins, and Sec tiles.
    1. For example, 02:47:59PM is a valid time format with one format string for each time component.

SEE ALSO

    Access your files securely from anywhere

      Zoho CRM Training Programs

      Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.

      Zoho CRM Training
        Redefine the way you work
        with Zoho Workplace

          Zoho DataPrep Personalized Demo

          If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.

          Zoho CRM Training

            Create, share, and deliver

            beautiful slides from anywhere.

            Get Started Now


              Zoho Sign now offers specialized one-on-one training for both administrators and developers.

              BOOK A SESSION









                                            You are currently viewing the help pages of Qntrl’s earlier version. Click here to view our latest version—Qntrl 3.0's help articles.




                                                Manage your brands on social media

                                                  Zoho Desk Resources

                                                  • Desk Community Learning Series


                                                  • Digest


                                                  • Functions


                                                  • Meetups


                                                  • Kbase


                                                  • Resources


                                                  • Glossary


                                                  • Desk Marketplace


                                                  • MVP Corner


                                                  • Word of the Day


                                                    Zoho Marketing Automation

                                                      Zoho Sheet Resources

                                                       

                                                          Zoho Forms Resources


                                                            Secure your business
                                                            communication with Zoho Mail


                                                            Mail on the move with
                                                            Zoho Mail mobile application

                                                              Stay on top of your schedule
                                                              at all times


                                                              Carry your calendar with you
                                                              Anytime, anywhere




                                                                    Zoho Sign Resources

                                                                      Sign, Paperless!

                                                                      Sign and send business documents on the go!

                                                                      Get Started Now




                                                                              Zoho TeamInbox Resources



                                                                                      Zoho DataPrep Resources



                                                                                        Zoho DataPrep Demo

                                                                                        Get a personalized demo or POC

                                                                                        REGISTER NOW


                                                                                          Design. Discuss. Deliver.

                                                                                          Create visually engaging stories with Zoho Show.

                                                                                          Get Started Now









                                                                                                              • Related Articles

                                                                                                              • Duration formats supported in Zoho DataPrep

                                                                                                                Duration is the difference between two time values. A duration format can be formed using different format strings such as D, H, SSSSSS, etc. Zoho DataPrep supports the following format strings: Format String Description Range D Indicates the Days 0 ...
                                                                                                              • Unify date formats

                                                                                                                DataPrep allows you to modify the format of the date values in a column to be in a uniform manner using the unify date format transform. To change the format of date values 1. Right-click the column with date values and select the Unify date format ...
                                                                                                              • Zoho Creator connector for Zoho DataPrep

                                                                                                                Zoho DataPrep allows you to seamlessly bring in your forms from Zoho Creator using the Zoho Creator connector. Using the connector, you can import your forms from Creator to perform data cleanup, data migration, data sync and data backup in more than ...
                                                                                                              • Ruleset in Zoho DataPrep

                                                                                                                This page covers the following sections: What is a Ruleset? Data source settings Ruleset operations Options with individual rules Ruleset Templates What is a Ruleset? Each transform applied on the dataset is stored in DataPrep as a rule, in order of ...
                                                                                                              • Zoho DataPrep's Home page

                                                                                                                The home page gives you a brief overview of the workspaces you own, where they are located, and the workspaces that are shared with you. From Zoho DataPrep's home page, you can manage your Zoho DataPrep account, create and manage workspaces. The ...
                                                                                                                Wherever you are is as good as
                                                                                                                your workplace

                                                                                                                  Resources

                                                                                                                  Videos

                                                                                                                  Watch comprehensive videos on features and other important topics that will help you master Zoho CRM.



                                                                                                                  eBooks

                                                                                                                  Download free eBooks and access a range of topics to get deeper insight on successfully using Zoho CRM.



                                                                                                                  Webinars

                                                                                                                  Sign up for our webinars and learn the Zoho CRM basics, from customization to sales force automation and more.



                                                                                                                  CRM Tips

                                                                                                                  Make the most of Zoho CRM with these useful tips.



                                                                                                                    Zoho Show Resources