Create buckets - Date

You can classify a group of date values or individual dates using the bucketing technique.

The bucketing options for a date column are:

Weekday or not
Month of the year 
Week of the year 
Week of the month 
Day of the year 
Day of the month
Minute of the hour 
Time of day
Custom date range

Weekday or not

This option lets you create a new column next to the date column to classify weekdays and weekends.

To apply the Weekday or Not option to a date column:

Right-click the date column in the DataPrep Studio page.
Select Create Buckets from the context menu.
Select the Weekday or not option from the Select bucket options dropdown in the Transform panel.
You can set the days of the week under the sections Weekdays or Weekends.
Enter a name for your new column in the New column name field.
A preview of the new column will be shown in the data grid.
Click Apply .

Month of the year

This option lets you create a new column next to the date column to classify the months of a year. To apply the Month of the year option to a date column:

Right-click the date column in the DataPrep Studio page.
Select the Create Buckets option from the context menu.
In the Transform panel, select the Month of the year option from the Select bucket options dropdown.
Enter a name for your new column in New column name.
Choose one of the options from the Create Buckets Using section and fill in the required fields (see detailed descriptions below):

Custom range
Specific values

A preview of the new column will be shown in the data grid.
Click Apply .

Custom range

You can use a custom range to input the conditions to determine the bucket labels.

To do this, enter conditions using these comparative operators:

Equal to (=), more than (>), or more than or equal to (>=), as part of the Start condition .
Less than (<), or less than or equal to (<=), as part of the End condition .

For example, the start condition to classify the date if it falls in the first half of the year will be greater than or equal to 1 (1st month).

The end condition will be less than or equal to 6 (6th month). Enter the bucket label as H1, 2018.

The start condition to classify the date if it falls in the second half of the year will be greater than 6.

The end condition will be less than or equal to 12. Enter the bucket label as H2, 2018.

If the selected column has values that don't fit any condition, it will be marked as "N/A". You can edit this label in the Label for unmatched values field.

Use the + button to add another condition below the current one or the - button to delete the current condition.

The buckets will be in the new column, next to each value in the selected date column that fits the range.

Specific values

You can use the Specific Values option to label specific values from the selected numerical column as a bucket.

To do this, enter conditions using these options:

In
Not in

For example, if you want to bucket the months based on a season like Autumn, enter the months with the condition "in" and enter the label name under Bucket label as "Autumn" and click Apply .

If you choose the "not in" condition, every other value which is not mentioned in this condition will get bucketed under one label.

Week of the year

This option lets you create a new column next to the date column to classify weeks of the year.

To apply the Week of the year option to a date column:

Right-click on the date column in the DataPrep Studio page.
Select the Create Buckets option from the context menu.
Select the Week Count option from the Select bucket options dropdown in the Transform panel.
Enter a name for your new column in New Column name .
Choose one of the options in the Create Buckets Using section and fill in the required fields (see detailed descriptions below):

Custom range
Specific values

A preview of the new column will be shown in the data grid.
Click Apply .

Custom range

You can use the Custom Range option to input the conditions to determine bucket labels.

To do this, provide conditions using these comparative operators:

Equal to (=), more than (>), or more than or equal to (>=), as part of the Start condition.
Less than (<), or less than or equal to (<=), as part of the End condition.

For example, the start condition to classify the date if it falls in the first half of the year will be greater than or equal to 1 (1st week).

The end condition will be less than or equal to 26 (26th week). Enter "H1, 2018" as the bucket label.

The start condition to classify the date if it falls in the second half of the year will be greater than 26.

The end condition will be less than or equal to 52. Enter "H2, 2018" as the bucket label.

If the selected column has values that don't fit any condition, they will appear as "N/A". You can edit this label in the Label for unmatched values option.

Use the + button to add another condition below the current one, or the - button to delete the current condition.

Specific values

You can use the Specific Values option to label specific values from the selected numerical column as a bucket.

To do this, provide the following conditions:

In
Not in

For example, if there is a high volume of sales in a grocery store during specific weeks of the year such as holidays, you can select the Specific Values option, select the "In" condition and enter the week numbers, e.g.- 45, 46, 47. Enter the Bucket label as "High sales week" and click apply.

You can enter the same weeks in the "Not in" condition. The ones that are not in the mentioned months can be named "Low sales week".

The ones that do not match the condition will have the default value "N/A".

Week of the month

This option lets you create a new column next to the date column to classify the weeks in a month.

To apply the Week of the Month option to a date column:

Right-click the date column in the DataPrep Studio page.
Select the Create Buckets option from the context menu.
In the Transform panel, select the Week of the month option from the Select bucket options dropdown.
Enter a name for your new column in the New column name field.
Choose one of the options in the Create buckets using section and fill in the required fields (see detailed descriptions below):

Custom range
Specific values

A preview of the new column will be shown in the data grid.
Click Apply .

Custom range

You can use the Custom Range option to enter the conditions to determine bucket labels.

To do this, enter conditions using these comparative operators:

Equal to (=), more than (>), or more than or equal to (>=), as part of the Start condition
Less than (<), or less than or equal to (<=), as part of the End condition

For example, if the first two weeks of every month have a high volume of sales of your product, you can classify the weeks using start and end conditions: >= 1 and <=2 as "high sales" and >2 and <=4 as "low sales".

If the selected column has values that don't fit any condition, it will be marked as "N/A". You can edit this label in the Label for unmatched values option.

Use the + button to add another condition below the current one, or the - button to delete the current condition.

Specific values

You can use the Specific Values option to label specific values from the selected numerical column as a bucket.

For example, if a certain week of each month has a high volume of sales in your company, you can select the Specific Values option, enter the week number, and label it as "Peak sales week".

The entries that do not match the condition will have the default value "N/A".

Day of the year

This option lets you create a new column next to the date column to classify the days in a year.

To apply the Day of the year option to a date column:

Right-click the date column in the DataPrep Studio page.
Select the Create Buckets option from the context menu.
Select Day of the year from the Select bucket options dropdown in the Transform panel.
Enter a name for your new column in the New column name field.
Choose one of the options in the Create buckets using section and fill in the required fields (see detailed descriptions below):

Custom range
Specific values

A preview of the new column will be shown in the data grid.
Click Apply .

Custom range

You can use the Custom Range option to input the conditions to determine bucket labels.

To do this, enter conditions using these comparative operators:

Equal to (=), more than (>), or more than or equal to (>=), as part of the Start condition
Less than (<), or less than or equal to (<=), as part of the End condition

For example, if there is a high volume of sales in your grocery store during the holiday season, you can select the Custom Range option and enter the Start and End conditions as >= 300 and <=356 to specify the range.

If the selected column has values that don't fit any condition, it will be marked "N/A". You can edit this label in the Label for unmatched values option.

Use the + button to add another condition below the current one, or the - button to delete the current condition.

Specific values

You can use the Specific Values option to label specific values from the selected numerical column as a bucket.

For example, if your business sees a high volume of sales on the first 2 days of every month, you can select the Specific values option and enter the day numbers as: 1, 2, 32, 33, 60, 61, and so on through to 334 and 335. Assuming the rest of the month sees low sales, you can select the "Not in" condition to label the days which do not fall under high sales volume. The ones that do not match the condition will have the default value "N/A".

Day of the month

This option lets you create a new column next to the date column to classify the days in a month.

To apply the Day of the month option to a date column:

Right-click the date column in the DataPrep Studio page.
Select the Create Buckets option from the context menu.
Select the Day of the month option from the Select bucket option dropdown in the Transform panel.
Enter a name for your new column in the New column name field.
Choose one of the options in the Create buckets using section and fill in the required fields (see detailed descriptions below):

Custom range
Specific values

6. Click Apply .

Custom range

You can use the Custom Range option to input the conditions to determine bucket labels.

To do this, enter conditions using these comparative operators:

Equal to (=), more than (>), or more than or equal to (>=) as part of the Start condition
Less than (<), or less than or equal to (<=) as part of the End condition

For example, if you need to schedule different team meetings during the first half and the second half of the month, you can enter the start and end conditions using comparison operators:

If the day is between >= 1 and <=15, the label is "Technical discussion"
If the day is between >15 and <=31, the label is "Status update"

If the selected column has values that don't fit any condition, it will be marked "N/A". You can edit this label in the Label for unmatched values option.

Use the + button to add another condition below the current one, or the - button to delete the current condition.

Specific values

You can use the Specific Values option to label specific values from the selected numerical column as a bucket.

For example, if you want to schedule team meetings on the 6th and 20th of each month, you can enter 6 and 20 and label them as "Project management meet" and "Status meet".

The dates that do not match the condition will have the default value "N/A".

Minute of the hour

This option lets you create a new column against the date-time column classifying hours of time in a day.

To apply the Time of Day option to a date column:

Right-click the date-time column in the DataPrep Studio page.
Select the Create Buckets option from the context menu.
Select the Minute of the hour option from the Select bucketing method dropdown in the Transform panel.
Enter a name for your new column in the New column name field.
Choose one of the options in the Create buckets using section and fill in the required fields (see detailed descriptions below):

Custom range
Specific values

A preview of the new column will be shown in the data grid.
Click Apply.

Custom range

You can use the Custom Range option to input the conditions to determine bucket labels.

To do this, enter conditions using these comparative operators.

Equal to (=), more than (>), or more than or equal to (>=) as part of the Start condition
Less than (<), or less than or equal to (<=) as part of the End condition

For example, if you need to specify meeting timings in your team, you can enter the start and end conditions using the comparison operators: >= 1 and < 3 as "Status update" and >=3, and <10 as "Product update".

If the selected column has values that don't fit any condition, it will be marked "N/A". You can edit this label in the Label for unmatched values option.

Use the + button to add another condition below the current one, or the - button to delete the current condition.

Specific values

You can use the Specific Values option to label specific values from the selected numerical column as a bucket.

For example, if you want to schedule team meetings and first 10 minutes of each hour it is conducted, you can enter the minutes in which the specific meeting is to be conducted and label them as "Status update".

The dates that do not match the condition will have the default value "N/A". You can edit this label in the Label for unmatched values option.

Time of day

This option lets you create a new column against the date-time column classifying hours of time in a day.

To apply the Time of Day option to a date column:

Right-click the date-time column in the DataPrep Studio page.
Select the Create Buckets option from the context menu.
Select the Time of day option from the Select bucketing method dropdown in the Transform panel.
Enter a name for your new column in the New column name field.
Choose one of the options in the Create buckets using section and fill in the required fields (see detailed descriptions below):

Custom range
Specific values

A preview of the new column will be shown in the data grid.
Click Apply .

Custom range

You can use the Custom Range option to input the conditions to determine bucket labels.

To do this, enter conditions using these comparative operators.

Equal to (=), more than (>), or more than or equal to (>=) as part of the Start condition

Less than (<), or less than or equal to (<=) as part of the End condition

For example, if you need to specify shift timings in your team, you can enter the start and end conditions using the comparison operators: >= 7AM and < 3PM as "Morning shift", >=3PM and <11PM as "Noon shift", and >=12AM and <7AM as "Night shift".

If the selected column has values that don't fit any condition, it will be marked as "N/A". You can edit this label in the Label for unmatched values option.

Use the + button to add another condition below the current one, or the - button to delete the current condition.

Custom date range

This option lets you create a new column next to the date column to classify a range of dates and label them.

To apply the Custom Date Range option to a date column:

Right-click the date column in the DataPrep Studio page.
Select the Create Buckets option from the context menu.
Select the Custom date range option from the Select bucketing method dropdown in the Transform panel.
Enter a name for your new column in the New column name field.
Select the from date and to date using the date picker and enter the bucket label.
For example, to label a range of dates during the holiday season, you can select the Start condition as December 6th, and the End condition as December 10th and label it as "Holidays".
If the selected column has values that don't fit any condition, it will be marked as "N/A". You can edit this label in the Label for unmatched values option.
A preview of the new column will be shown in the data grid.
Click Apply .

To apply filters

If you want to apply some filters along with this transform, you can use the filters functionality.

1. Click the Filters tab.

2. Click the

icon and add the required columns in the Filters section. You can also reorder the filters using the drag and drop method.

3. For every column added, you can select one of the following options from the drop-down:

Actual: This option lets you filter rows based on the actual values in the column. Click here to know more.
Data quality: This option lets you filter rows based on the quality of data in the column. Click here to know more.
Patterns: This option helps you filter rows based on the data patterns in the selected column. Click here to know more.
Seasonal: This option helps you filter rows based on the seasonal parameters such as quarter, month, week etc. Click here to know more.
Outliers: This option allows you to filter rows based on the outliers present in the data of the selected column. Click here to know more.

Note: The filter options are displayed based on the datatype of the column added for the filter.

4. When you add more than one filter to the Filters section, the logical operators, AND or OR appear next to the filters. You can click to toggle the logical operator between AND and OR.

Using the logical operators, you can combine the conditions and apply logic to determine the rule of precedence. The final expression is displayed in the Criteria expression box. You can click Edit to alter the default expression using logical operators and parenthesis to specify the precedence or the sequential order as to which condition should be evaluated first. Click Save after making the required changes.

For example, In the expression, ((1 AND 2) OR (3 AND 4)) , at first the condition ( 1 AND 2 ) will be executed and the condition ( 3 AND 4 ) will be executed next. Thirdly, since, the OR operator is used, the filter will be applied when either or both the conditions is true.

5. You can further drill down to choose specific values based on the filter option selected for each filter, in the next section.

For example, in the above screenshot, the Data quality option is selected for the All columns filter in the Filters section. Based on the selection, further options to filter specific values are displayed in the All columns (Data quality) section.

6. You can choose to include or exclude the selected items in the last section.

7. If you want to remove all the filters for some reason, you can use the Clear button.

8. A live preview of the filter transform is shown as you make changes.

9. Click the Apply button to apply the transform along with the filters.

SEE ALSO

Create buckets in number columns

Create buckets in text columns

Learn about Filters transform

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.

Redefine the way you work

with Zoho Workplace

Start your free trial

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.

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

Zoho Workerly Home
Forums
Connect With Us:

Zoho Recruit Home
Forums
Connect With Us:

Start tracking your website metrics today

Use PageSense to understand what your visitors are looking for, how they are behaving, and where they are facing problems on your website.

Ready to improve your website's performance?

Install the PageSense code snippet on your site in a matter of minutes and start collecting in-depth data about the website visitors to grow your business.

Track and measure every business goal

Set up goals in PageSense to measure every single action performed by visitors on your website like button or link clicks, form submissions, and page engagements.

Understand the customer journey on your website

Create funnels in PageSense to quickly see which pages visitors use to enter your website, where they navigate to next, and which pages they decide to leave without converting.

Visualize your visitor's behavior with color codes

Set up heatmaps in PageSense to see where users have clicked more, how far they've scrolled, and on which parts of a page they've spent the most time using color-coded patterns in reports.

Measure customer engagement with your web forms

Use form analytics in PageSense to see how people interact with different fields in your form, whether they complete the form successfully or not, and where exactly they drop out on your form.

Record real visitor interactions on your website

Use session recordings in PageSense to watch a video of all the visitor actions performed on your website including the pages they navigate, the buttons they click, the UX issues they face, and more.

Test and optimize webpages for better conversions

Run A/B or Split URL tests in PageSense to figure out which version of your web page works best for your business and results in the best conversion rate.

Give each customer a unique website experience

Use personalization in PageSense to deliver customized versions of your website for every individual customer based on their demographics, local weather, browsing history, and more.

Grow your business through customer feedback

Run polls on your website using PageSense to understand what your customers think about your products/services and what needs improvement on your site.

Send timely updates that customers love

Use web push notifications in PageSense to schedule and notify your customers about an upcoming flash sale, product releases, promotional coupons, and a lot more that can spark conversions on your website.

Advertise your business to website visitors

Use pop-ups in PageSense to instantly grab the attention of visitors by showing attractive signup offers, coupon code discounts, or email newsletters that can eventually convert them into subscribers.

Access more advanced settings in PageSense

Use PageSense's advanced features like creating mutually exclusive groups, enabling cross-domain tracking, configuring customized project JS, and more to get deeper insights about your website.

Try easy-to-use extensions

Download the PageSense extension app available for your web browser with a few clicks and start collecting all of your required website metrics in real time.

Discover your favorite integrations with PageSense

Get a deeper look at your website's data by seamlessly integrating PageSense with a host of popular third-party apps like Google Analytics, Mixpanel, Intercom, and more.

Quick Links	Workflow Automation	Data Collection
Web Forms	Enterprise	Begin Data Collection
Interactive Forms	Workplace	Data Collection App
CRM Forms	Customer Service	Accessible Forms
Digital Forms	Marketing	Forms for Small Business
HTML Forms	Education	Forms for Enterprise
Contact Forms	E-commerce	Forms for any business
Lead Generation Forms	Healthcare	Forms for Startups
Wordpress Forms	Customer onboarding	Order Forms for Small Business
No Code Forms	Construction	RSVP tool for holidays
Free Forms	Travel
Prefill Forms	Non-Profit
Intake Forms	Legal	Mobile App
Form Designer	HR	Mobile Forms
Card Forms	Food	Offline Forms
Assign Forms	Photography	Mobile Forms Features
Translate Forms	Real Estate	Kiosk in Mobile Forms
Electronic Forms
Notification Emails for Forms	Alternatives	Security & Compliance
Holiday Forms	Google Forms alternative	GDPR
Form to PDF	Jotform alternative	HIPAA Forms
Email Forms		Encrypted Forms
Embeddable Forms		Secure Forms
Drag and Drop form builder		WCAG

New to Zoho CRM Plus?

Deliver unforgettable customer experiences

ACCESS ZOHO CRM PLUS

New to Zoho CRM Plus?

Deliver unforgettable customer experiences

ACCESS ZOHO CRM PLUS

New to Zoho Marketing Plus?

Everything you need to run your marketing

ACCESS ZOHO MARKETING PLUS

New to Zoho Marketing Plus?

Everything you need to run your marketing

ACCESS ZOHO MARKETING PLUS

Zoho Pagesense Resources

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.

New to Zoho Survey?

Access Zoho Survey

Latest Feature Updates

Explore our Pricing & Plans

Zoho Survey Resources

Android Mobile Surveys

Insurvey Blogs

Insight Blogs

Tips & Tricks

New to Zoho Social?

Manage your brands on social media

Access Zoho Social

Zoho Desk Resources

Desk Community Learning Series
Digest
Functions
Meetups
Kbase
Resources
Glossary
Desk Marketplace
MVP Corner
Word of the Day

Zoho Sheet Resources

New to Zoho Forms?

Latest Feature Updates

Explore our Pricing & Plans

Zoho Forms Resources

Secure your business

communication with Zoho Mail

Get started

Mail on the move with

Zoho Mail mobile application

Go Mobile

Stay on top of your schedule

at all times

Get started

Carry your calendar with you

Anytime, anywhere

Get started

Latest Announcements

New to Zoho Sign?

Zoho Sign Resources

Sign, Paperless!

Sign and send business documents on the go!

Get Started Now

Zoho SalesIQ Resources

New to Zoho TeamInbox?

Zoho TeamInbox Resources

Connect with us

New to Zoho ZeptoMail?

LEARN MORE

Latest Feature Updates

Zoho DataPrep Resources

New to Zoho DataPrep?

Access Zoho DataPrep

Zoho DataPrep Demo

Get a personalized demo or POC

Design. Discuss. Deliver.

Create visually engaging stories with Zoho Show.

Get Started Now

New to Zoho Workerly?

Access Zoho Workerly

New to Zoho Recruit?

Access Zoho Recruit

New to Zoho CRM?

Signup Now

Access Zoho CRM

Latest Feature Updates

New to Zoho Projects?

Access Zoho Projects

New to Zoho Sprints?

Access Zoho Sprints

New to Zoho Assist?

Access Zoho Assist

New to Bigin?

Signup Now

Access Bigin

Latest Feature Updates

Related Articles
Create buckets - Number
The bucketing transformation groups values in multiple ranges or buckets for easier understanding of highly scaled data. The granular details of such data can prevent us from getting a broad view of the data. This form of grouping values in a range ...
Create buckets - Text
You can choose to implement bucketing in a text column using conditions. Consider a situation where you need to assign people to specific teams. You can implement the logic of adding values to a bucket, which in this case is the team name. Name Team ...
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 ...
Change date format
DataPrep offers a wide range of predefined date and time formats. Choose to apply a predefined datetime format from the template or create your own custom format. Your custom format is saved to the template and can be used again in the future. To ...
Extract from date
DataPrep provides various options to extract part of date values into a new column. The portions which can be extracted are: Year Quarter and year Month and year Week and year Date Time Quarter Month Week Day of the week Day of the month To extract ...