Table Toolbox format Jira date

Question

Hello,

I'm try to embed Jira issue in Table Toolbox in Confluence and rearrange the data. I'm struggle with the date format.

The issues I get from Jira have the following date format.

And as soon as I put these Jira issues in a Table Toolbox, the format change.

Finally I realize that sorting date doesn't work properly and I assume it's because of this weird date formatting. But I'm not 100% sure.  In the follwing screenshot, lines are supposed to be ordered on Start Date by ASC.

Could you tell me how I can format it correctly? Also, is it possible to format date like Confluence date (like the ones we get by using the // shortcut).

Thanks for your help,

Aurélien

Stiltsoft support · Answer

Hi @Aurélien Thazet ,

As your Jira Issues macro returns the following format judging by the first screenshot "7 Oct 2024", then the corresponding date format in the Table Transformer macro settings should be "d M yy" (and the variant "07 Oct 2024" corresponds to the "dd M yy" format).

Also your first screenshot doesn't look like a pure Jira Issues macro: the vertical borders are present. Are you sure that it is pure Jira Issues macro on a Confluence page without any other macros in the page view mode?

For example, on the second screenshot when you wrap your Jira Issues macro in the Table Toolbox macro there are no any vertical borders. The date format is 17/01/2025 that corresponds to the "dd/mm/yy" format for the Table Transformer macro.

The Table Toolbox macro (and the screenshot is taken when you "stand" on the Jira Issues table, the table is not transformed yet) is not supposed to change anything in the source table, it shows data as it is.

So, please check this moment once more: publish the page with the Jira Issues macro (as it is without any other macros) and the Jira Issues macro wrapped in the Table Toolbox macro (but without any Table Transformer macros on top) and compare the results.

If the date formats differ, please refer to our support for further investigation.

Aurélien Thazet · Answer

Hello,

Thanks for your answer. I tried several way to format date correctly.

* I selected the Date format: dd.mm.yy
* Here is my SQL request:

SELECT *,
FORMATDATE(T1.'Start date', "dd.mm.yy") AS 'Start Date'
FROM T*

I have three issues:

* Some dates return NaN.NaN.NaN value and I don't know why.
* The other dates switched the day and the month, which leds me to incorrect data.
* Some dates are just incorrect, don't know why.

Stiltsoft support · Answer

Hi @Aurélien Thazet ,

The Table Toolbox macro and the Table Transformer macro are provided by the app that we develop - Table Filter, Charts & Spreadsheets for Confluence.

You may go to the "Settings" tab of the Table Transformer macro and check if the date format there corresponds to the date format in your original Jira Issues table.

In general, to change the date format, you may use the FORMATDATE(T1.’Date’, “dd M yy”) function (the format in quotes is the desired output format).