Add Date Range Picker in Spreadsheet

Introduction

This guide demonstrates how to implement a date range picker in Joget DX using custom HTML and JavaScript code. A date range picker is an essential feature for applications that require users to select start and end dates, such as booking systems, project trackers, or report generators.

The implementation leverages Flatpickr’s robust functionalities, including range selection and month-year picking, ensuring a seamless and user-friendly experience. This tutorial also showcases custom editors integrated with Handsontable to enhance low-code project workflows.

By following this guide, users can:

Enable dynamic date range selection for enhanced user input.
Provide intuitive and customized month-year pickers for specific use cases.
Simplify development with reusable components in low-code environments.

How does it work?

To implement a date range picker in spreadsheets, use the Custom HTML field under the spreadsheet field in the form.

Enable the Handsontable instance to be interactable by pasting the following code into the HTML elements from Form Builder > Spreadsheet Configuration > UI > Custom Settings (JSON).
```
{
    "afterInit": function() {
        var hot = this;
        $(hot.rootElement).data("hot", hot);
    }
}
```
In the spreadsheet, set the format type as Text in the date range, month-year columns as below in the snippet.

Add the following script to the Custom HTML field.

For Joget DX 8.1.12 or earlier
DX 8.1.12 and earlier releases utilize Handsontable version 6.2.2.
Please refer to the sample app (APP_kb_tillDx8.1.x_sPDateRangeandMonthPicker.jwa)

<link
  rel="stylesheet"
  href="https://cdn.jsdelivr.net/npm/flatpickr/dist/flatpickr.min.css"
/>
<script src="https://cdn.jsdelivr.net/npm/flatpickr"></script>
<script src="https://cdn.jsdelivr.net/npm/flatpickr/dist/plugins/rangePlugin.js"></script>
<script src="https://cdn.jsdelivr.net/npm/flatpickr/dist/plugins/monthSelect/index.js"></script>

<style>
  .flatpickr-current-month .numInputWrapper {
    width: 74px;
  }
  .flatpickr-calendar.animate.open {
    min-width: 410px;
  }
  .flatpickr-rContainer {
    width: 100%;
  }
  .flatpickr-monthSelect-months {
    display: flex;
    justify-content: space-around;
    width: 100%;
  }
  span.flatpickr-monthSelect-month {
    padding: 0 4px;
    cursor: pointer;
  }
  span.flatpickr-monthSelect-month:hover {
    background: #f0f0f0;
  }
</style>

<script>
  $(document).ready(function () {
    const extractMonthYear = (date) => {
      const options = { year: "numeric", month: "2-digit" };
      return new Date(date).toLocaleDateString("en-CA", options);
    };
    const formatDate = (date) => {
      const options = { year: "numeric", month: "2-digit", day: "2-digit" };
      return new Date(date).toLocaleDateString("en-CA", options);
    };
    const formatDateRange = (start, end) => {
      return `${formatDate(start)} to ${formatDate(end)}`;
    };

    // Custom Date Range Editor
    const DateRangeEditor = Handsontable.editors.TextEditor.prototype.extend();

    DateRangeEditor.prototype.createElements = function () {
      Handsontable.editors.TextEditor.prototype.createElements.apply(
        this,
        arguments
      );

      // Create a Flatpickr instance for date range selection
      this.datePicker = document.createElement("input");
      this.datePicker.type = "text";
      this.datePicker.style.position = "absolute";
      this.datePicker.style.visibility = "hidden";
      this.datePicker.style.zIndex = "9999999";
      this.instance.rootElement.appendChild(this.datePicker);

      this.flatpickrInstance = flatpickr(this.datePicker, {
        mode: "range",
        dateFormat: "Y-m-d",
        onChange: (selectedDates) => {
          if (selectedDates.length === 2) {
            const value = formatDateRange(selectedDates[0], selectedDates[1]);

            // Update the Handsontable cell value
            this.instance.setDataAtCell(this.row, this.col, value);
            hot.setDataAtRowProp(this.row, this.col, value);
          }
        },
        onClose: () => {
          this.finishEditing();
          this.datePicker.style.display = "none";
        },
        onOpen: () => {
          this.datePicker.style.display = "block";
        },
      });
    };

    DateRangeEditor.prototype.open = function () {
      const cellOffset = this.TD.getBoundingClientRect();
      const containerOffset = this.instance.rootElement.getBoundingClientRect();

      this.datePicker.style.top = `${
        cellOffset.top - containerOffset.top + this.TD.offsetHeight
      }px`;
      this.datePicker.style.left = `${
        cellOffset.left - containerOffset.left
      }px`;
      this.datePicker.style.display = "block";

      const cellValue = this.instance.getDataAtCell(this.row, this.col);
      if (cellValue && cellValue.includes(" to ")) {
        const [startDate, endDate] = cellValue.split(" to ");
        this.flatpickrInstance.setDate([
          new Date(startDate),
          new Date(endDate),
        ]);
      } else {
        this.flatpickrInstance.clear();
      }
      this.flatpickrInstance.open();
    };

    DateRangeEditor.prototype.close = function () {
      Handsontable.editors.TextEditor.prototype.close.apply(this, arguments);
    };

    DateRangeEditor.prototype.destroy = function () {
      this.flatpickrInstance.destroy();
      this.datePicker.remove();
      Handsontable.editors.TextEditor.prototype.destroy.apply(this, arguments);
    };

    // Custom Month-Year Picker Editor
    const MonthYearEditor = Handsontable.editors.TextEditor.prototype.extend();

    MonthYearEditor.prototype.createElements = function () {
      Handsontable.editors.TextEditor.prototype.createElements.apply(
        this,
        arguments
      );

      // Create a Flatpickr instance for month-year selection
      this.datePicker = document.createElement("input");
      this.datePicker.type = "text";
      this.datePicker.style.position = "absolute";
      this.datePicker.style.visibility = "hidden";
      this.datePicker.style.zIndex = "9999999";

      this.instance.rootElement.appendChild(this.datePicker);

      this.flatpickrInstance = flatpickr(this.datePicker, {
        plugins: [
          new monthSelectPlugin({
            shorthand: true,
            dateFormat: "Y-m",
            altFormat: "F Y",
          }),
        ],
        onChange: (selectedDates) => {
          if (selectedDates.length === 1) {
            const selectedMonthYear = extractMonthYear(selectedDates[0]);
            this.instance.setDataAtCell(this.row, this.col, selectedMonthYear);
            hot.setDataAtRowProp(this.row, this.col, selectedMonthYear);
          }
        },
        onClose: () => {
          this.finishEditing();
          this.datePicker.style.display = "none";
        },
        onOpen: () => {
          this.datePicker.style.display = "block";
        },
      });
    };

    MonthYearEditor.prototype.open = function () {
      const cellOffset = this.TD.getBoundingClientRect();
      const containerOffset = this.instance.rootElement.getBoundingClientRect();

      this.datePicker.style.top = `${
        cellOffset.top - containerOffset.top + this.TD.offsetHeight
      }px`;
      this.datePicker.style.left = `${
        cellOffset.left - containerOffset.left
      }px`;
      this.datePicker.style.display = "block";

      const cellValue = this.instance.getDataAtCell(this.row, this.col);
      if (cellValue && cellValue?.includes("-")) {
        this.flatpickrInstance.setDate([new Date(cellValue)]);
      } else {
        this.flatpickrInstance.clear();
      }

      this.flatpickrInstance.open();
    };

    MonthYearEditor.prototype.close = function () {
      Handsontable.editors.TextEditor.prototype.close.apply(this, arguments);
    };

    MonthYearEditor.prototype.destroy = function () {
      this.flatpickrInstance.destroy();
      this.datePicker.remove();
      Handsontable.editors.TextEditor.prototype.destroy.apply(this, arguments);
    };

    // Get the Handsontable instance
    var hot = FormUtil.getField("sp_custom").data("hot");
    console.log("hot :>> ", hot);
    if (hot) {
      hot.updateSettings({
        columns: hot.getSettings().columns.map((column, index) => {
          if (index === 0) {
            // apply the column position index (0 for the first column)
            return {
              ...column,
              editor: DateRangeEditor,
            };
          } else if (index === 1) {
            // Apply Month-Year Picker Editor to the fourth column
            return {
              ...column,
              editor: MonthYearEditor,
            };
          }
          return column;
        }),
      });
    }
  });
</script>

For Joget DX 8.2.x or later
DX 8.2.x utilizes Handsontable version 15.2.0,
Please refer to the sample app (APP_kb_dx8.2.x_sPDateRangeandMonthPicker.jwa)

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/flatpickr@4.6.13/dist/flatpickr.min.css">
<script src="https://cdn.jsdelivr.net/npm/flatpickr@4.6.13/dist/flatpickr.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/flatpickr@4.6.13/dist/plugins/rangePlugin.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/flatpickr@4.6.13/dist/plugins/monthSelect/index.min.js"></script>
 
<style>
  .flatpickr-current-month .numInputWrapper {
    width: 74px;
  }
  .flatpickr-calendar.animate.open {
    min-width: 410px;
  }
  .flatpickr-rContainer {
    width: 100%;
  }
  .flatpickr-monthSelect-months {
    display: flex;
    justify-content: space-around;
    width: 100%;
  }
  span.flatpickr-monthSelect-month {
    padding: 0 4px;
    cursor: pointer;
  }
  span.flatpickr-monthSelect-month:hover {
    background: #f0f0f0;
  }
</style>
 
<script>
  $(document).ready(function () {
    const extractMonthYear = (date) => {
      const options = { year: "numeric", month: "2-digit" };
      return new Date(date).toLocaleDateString("en-CA", options);
    };
    const formatDate = (date) => {
      const options = { year: "numeric", month: "2-digit", day: "2-digit" };
      return new Date(date).toLocaleDateString("en-CA", options);
    };
    const formatDateRange = (start, end) => {
      return `${formatDate(start)} to ${formatDate(end)}`;
    };
 
    // Custom Date Range Editor for Handsontable 15.2.0
    class DateRangeEditor extends Handsontable.editors.TextEditor {

        
      constructor(hotInstance, row, col, prop, value, cellProperties) {
        super(hotInstance, row, col, prop, value, cellProperties);
        this.datePicker = null;
        this.flatpickrInstance = null;
      }

      createElements() {
        
        super.createElements();

        this.datePicker = document.createElement("input");
        this.datePicker.type = "text";
        this.datePicker.style.position = "absolute";
        this.datePicker.style.visibility = "hidden";
        this.datePicker.style.zIndex = "9999999";
        
        const targetBody = this.hot.rootDocument ? this.hot.rootDocument.body : document.body;
        targetBody.appendChild(this.datePicker);

        this.flatpickrInstance = flatpickr(this.datePicker, {
          mode: "range",
          dateFormat: "Y-m-d",
          onChange: (selectedDates) => {
            console.log('DateRange onChange - selectedDates:', selectedDates);
            if (selectedDates.length === 2) {
              const value = formatDateRange(selectedDates[0], selectedDates[1]);
              // Update the Handsontable cell value
              this.hot.setDataAtCell(this.row, this.col, value);
              setTimeout(() => {
                this.finishEditing();
                this.datePicker.style.display = "none";
              }, 100);
            }
          },
          onClose: () => {
            this.finishEditing();
            this.datePicker.style.display = "none";
          },
          onOpen: () => {
            this.datePicker.style.display = "block";
          },
        });
      }

      open() {
        
        if (!this.datePicker || !this.flatpickrInstance) {
          console.log('Recreating elements in open method');
          this.createElements();
        }
        
        const cellOffset = this.TD.getBoundingClientRect();
        const targetBody = this.hot.rootDocument ? this.hot.rootDocument.body : document.body;
        const containerOffset = targetBody.getBoundingClientRect();

        this.datePicker.style.top = `${
          cellOffset.top - containerOffset.top + this.TD.offsetHeight
        }px`;
        this.datePicker.style.left = `${
          cellOffset.left - containerOffset.left
        }px`;
        this.datePicker.style.display = "block";

        const cellValue = this.hot.getDataAtCell(this.row, this.col);
        if (cellValue && cellValue.includes(" to ")) {
          const [startDate, endDate] = cellValue.split(" to ");
          this.flatpickrInstance.setDate([
            new Date(startDate),
            new Date(endDate),
          ]);
        } else {
          this.flatpickrInstance.clear();
        }
        setTimeout(() => {
            this.flatpickrInstance.open();
        }, 10);
      }

      close() {
        super.close();
      }

      destroy() {
        if (this.flatpickrInstance) {
          this.flatpickrInstance.destroy();
        }
        if (this.datePicker) {
          this.datePicker.remove();
        }
        super.destroy();
      }
    }

    // Custom Month-Year Picker Editor for Handsontable 15.2.0
    class MonthYearEditor extends Handsontable.editors.TextEditor {
      constructor(hotInstance, row, col, prop, value, cellProperties) {
        super(hotInstance, row, col, prop, value, cellProperties);
        this.datePicker = null;
        this.flatpickrInstance = null;
      }

      createElements() {
        super.createElements();

        // Create a Flatpickr instance for month-year selection
        this.datePicker = document.createElement("input");
        this.datePicker.type = "text";
        this.datePicker.style.position = "absolute";
        this.datePicker.style.visibility = "hidden";
        this.datePicker.style.zIndex = "9999999";

        // Use the correct way to append to document body in v15.2.0
        // Check if rootDocument exists, fallback to document if not
        const targetBody = this.hot.rootDocument ? this.hot.rootDocument.body : document.body;
        targetBody.appendChild(this.datePicker);

        this.flatpickrInstance = flatpickr(this.datePicker, {
          plugins: [
            new monthSelectPlugin({
              shorthand: true,
              dateFormat: "Y-m",
              altFormat: "F Y",
            }),
          ],
          onChange: (selectedDates) => {
            console.log('MonthYear onChange - selectedDates:', selectedDates);
            if (selectedDates.length === 1) {
              const selectedMonthYear = extractMonthYear(selectedDates[0]);
              this.hot.setDataAtCell(this.row, this.col, selectedMonthYear);
              // Close after selection for month-year picker
              setTimeout(() => {
                this.finishEditing();
                this.datePicker.style.display = "none";
              }, 100);
            }
          },
          onClose: () => {
            console.log('MonthYear onClose');
            this.finishEditing();
            this.datePicker.style.display = "none";
          },
          onOpen: () => {
            console.log('MonthYear onOpen');
            this.datePicker.style.display = "block";
          },
        });
      }

      open() {
        if (!this.datePicker || !this.flatpickrInstance) {
          console.log('Recreating elements in MonthYearEditor open method');
          this.createElements();
        }
        
        const cellOffset = this.TD.getBoundingClientRect();
        const targetBody = this.hot.rootDocument ? this.hot.rootDocument.body : document.body;
        const containerOffset = targetBody.getBoundingClientRect();

        this.datePicker.style.top = `${
          cellOffset.top - containerOffset.top + this.TD.offsetHeight
        }px`;
        this.datePicker.style.left = `${
          cellOffset.left - containerOffset.left
        }px`;
        this.datePicker.style.display = "block";

        const cellValue = this.hot.getDataAtCell(this.row, this.col);
        if (cellValue && cellValue?.includes("-")) {
          this.flatpickrInstance.setDate([new Date(cellValue)]);
        } else {
          this.flatpickrInstance.clear();
        }

        setTimeout(() => {
            this.flatpickrInstance.open();
        }, 10);
      }

      close() {
        super.close();
      }

      destroy() {
        if (this.flatpickrInstance) {
          this.flatpickrInstance.destroy();
        }
        if (this.datePicker) {
          this.datePicker.remove();
        }
        super.destroy();
      }
    }

    // Get the Handsontable instance
    var hot = FormUtil.getField("sp_custom").data("hot");
    console.log("hot :>> ", hot);
    if (hot) {
      hot.updateSettings({
        columns: hot.getSettings().columns.map((column, index) => {
          if (index === 0) {
            // apply the column position index (0 for the first column)
            return {
              ...column,
              editor: DateRangeEditor,
            };
          } else if (index === 1) {
            // Apply Month-Year Picker Editor to the second column
            return {
              ...column,
              editor: MonthYearEditor,
            };
          }
          return column;
        }),
      });
    }
  });
</script>

Adjust the index (0, 1) value in lines 202 and 208, respectively, according to your position of the date range field column.

Expected Outcome

You can now select dates in a date range picker in Joget DX by clicking on the date range cell in the spreadsheet, as demonstrated in the image below.

Download Sample App

Download the demo app for the Add Date Range Picker In Spreadsheet tutorial:

Created by Aadrian Last modified by Debanraj Ravindran on Aug 05, 2026

Tags:

Previous Merging Excel with PDF NextCreating a BeanShell Spreadsheet Validator

Add Date Range Picker in Spreadsheet

Summary

Prerequisites & Step-by-Step

Introduction

How does it work?

Expected Outcome

Related Documentation

Download Sample App

FAQ

Related Articles

Joget AI Agent is working