Date formatting

[Nags Subramanian]

i have a source table like with values like this

023 Q1			3	1	2	1	3		1	11
2023 Q3		1		2	2					5
2023 Q4							4		11	15
2024 Q1			2	2	1		1		2	8
2024 Q2	2			1	4					7
2024 Q3					2	1				3
2024 Q4			3	10	10	2	20	1	2	48
2025 Q1	1			8	6		21		4	40
2025 Q2					4		3			7
2025 Q3					6					6
2025 Q4				1	2		9		1	13
Total	3	1	8	25	39	4	61	1	21	163

i have written a table transformer macro with the query as

SELECT T1.'Quarter' as Quarter ,
case
when T1.'Quarter'="2023 Q1" then "Mar 31, 2023"
when T1.'Quarter'="2023 Q2" then "Jun 30, 2023"
when T1.'Quarter'="2023 Q3" then "Sep 30, 2023"
when T1.'Quarter'="2023 Q4" then "Dec 31, 2023"
when T1.'Quarter'="2024 Q1" then "Mar 31, 2024"
when T1.'Quarter'="2024 Q2" then "Jun 30, 2024"
when T1.'Quarter'="2024 Q3" then "Sep 30, 2024"
when T1.'Quarter'="2024 Q4" then "Dec 31, 2024"
when T1.'Quarter'="2025 Q1" then "Mar 31, 2025"
when T1.'Quarter'="2025 Q2" then "Jun 30, 2025"
when T1.'Quarter'="2025 Q3" then "Sep 30, 2025"
when T1.'Quarter'="2025 Q4" then "Dec 31, 2025"
end
as 'Quarter End Date',
T1.'Sum of Count AM' as 'AM',
T1.'Sum of Count CFO-GF' as'CFO-GF',
T1.'Sum of Count GCRG' as 'GCRG',
T1.'Sum of Count GWM' as 'GWM',
T1.'Sum of Count NCL' as 'NCL',
T1.'Sum of Count Other' as 'Other',
T1.'Sum of Count P&C & SB' as 'P&C & SB',
T1.'Sum of Count Treasury' as 'Treasury',
T1.'Sum of Count X-Divisional' as'X-divisional'
from T1

As a result i get the below table as result,

2023 Q1	1680217200000			3	1	2	1	3		1
2023 Q3	1696028400000		1		2	2
2023 Q4	1703980800000							4		11
2024 Q1	1711843200000			2	2	1		1		2
2024 Q2	1719702000000	2			1	4
2024 Q3	1727650800000					2	1
2024 Q4	1735603200000			3	10	10	2	20	1	2
2025 Q1	1743375600000	1			8	6		21		4
2025 Q2	1751238000000					4		3
2025 Q3	1759186800000					6
2025 Q4	1767139200000				1	2		9		1
Total		3	1	8	25	39	4	61	1	21

Quarter End Date column , instead of a date, displays number like 1680217200000.

case
when T1.'Quarter'="2023 Q1" then "Mar 31, 2023"
when T1.'Quarter'="2023 Q2" then "Jun 30, 2023"
when T1.'Quarter'="2023 Q3" then "Sep 30, 2023"
when T1.'Quarter'="2023 Q4" then "Dec 31, 2023"
when T1.'Quarter'="2024 Q1" then "Mar 31, 2024"
when T1.'Quarter'="2024 Q2" then "Jun 30, 2024"
when T1.'Quarter'="2024 Q3" then "Sep 30, 2024"
when T1.'Quarter'="2024 Q4" then "Dec 31, 2024"
when T1.'Quarter'="2025 Q1" then "Mar 31, 2025"
when T1.'Quarter'="2025 Q2" then "Jun 30, 2025"
when T1.'Quarter'="2025 Q3" then "Sep 30, 2025"
when T1.'Quarter'="2025 Q4" then "Dec 31, 2025"
end
as 'Quarter End Date',

i have set the date format as M dd, yy in the setting on the table transformaer macro settings.

could you please let me know how to correct this?

thanks

[Stiltsoft support]

Hi @Nags Subramanian ,

You may use the FORMATDATE function in your query:

when T1.'Quarter'="2023 Q1" then FORMATDATE("Mar 31, 2023")

[Nags Subramanian]

THANK YOU. i knew it would be as simple as that.

[Stiltsoft support]

Also as you set dates based not on some text condition (for example, if the project name is A, then Mar 31, 2023) but your conditions are based on real dates in a proper format, you may use a much more simple query:

SELECT *,
FORMATDATE(DATEADD(day, -1, DATEADD(quarter, 1, 'Quarter')), "M dd, yy")
AS 'Quarter End Date'
FROM T1

The date format in the Table Transformer macro settings should be set as yy Qi that corresponds to the "2023 Q1" format in your source table.

[Nags Subramanian]

oh this is much better, thank you.

[Nags Subramanian]

sorry, i am using this but results look different. i have the date format as yy Qi too. what seems to be wrong please?

SELECT T1.'Quarter' as Quarter ,
FORMATDATE(DATEADD(day, -1, DATEADD(quarter, 1, T1.'Quarter')), "M dd, yy")
AS 'Quarter End Date',

[Stiltsoft support]

Seems that your version of the Table Filter, Charts & Spreadsheets for Confluence app may be an outdated one. Please ask your Confluence administrator to check and update if required.

The current version is 11.2.0.