Setup a Filter on Date Column in DataFromDatabase

Excel does not support Date and Time data types unlike databases. Hence, while doing a SheetKraft import of the table having a column of date type from database to Excel, the values in that column appear in number format rather than date format.

While importing a table from database having a date column set to float data type, the filter condition will have the date in number format to fetch values in that column; whereas for a table imported from database having a date column set to date data type, the filter condition will have the date in text format (for example, '03-Apr-2018) to fetch values in that column. Providing the date as 03-04-2018 in the latter will create ambiguity if 03 and 04 is the day or month.

Following steps describe the setup of filter on date column in more detail:

1. Import the data from database to Excel.

Fig 1.1: Importing data from database

2. In the Data From Database window, select the table from the database by clicking on the ellipsis as highlighted in the following figure:

Fig 1.2: Data From Database window

3. Select the table from the “Select Table” window.

Fig 1.3: Selecting table

4. Table preview is shown after selecting the table from database.

Fig 1.4: Table preview

5. Once the data is imported from database, the date is displayed in number format; since the backend of SheetKraft stores the date in number format.

Fig 1.5: Data imported to Excel

NOTE: To display the cells in the Date column in date format, press Ctrl + Shift + 3, and press Ctrl + Shift + ~ to convert it back to number form. This is only for display purpose and has no effect on the backend of SheetKraft.

6. For retrieving data of any particular date, for example 3rd of April 2018 (43193), add the filter condition in the text format, that is, '03-Apr-2018 as shown in figure Fig 1.6.

Fig 1.6: Filter condition

7. Following is the output with date matching the filter condition, that is, '03-Apr-2018:

Fig 1.7: Output