Date displayed as numbers.

[Nagarajan Kalyana Subramanian]

Hi, i am using table transformer macro and wrote the below statement, to display date in the table. but the table is displayed with numbers, i have set the config in the table transformer setting as 'M dd, yy"

case when T1.'End date' >= "Jan 1, 2023" and T1.'End date' <= "Mar 31, 2023" then "Jan 1, 2023"
when T1.'End date' >= "Apr 1, 2023" and T1.'End date' <= "Jun 30, 2023" then "Apr 1, 2023"
when T1.'End date' >= "Jul 1, 2023" and T1.'End date' <= "Sep 30, 2023" then "Jul 1, 2023"
when T1.'End date' >= "Oct 1, 2023" and T1.'End date' <= "Dec 31, 2023" then "Oct 1, 2023"
when T1.'End date' >= "Jan 1, 2024" and T1.'End date' <= "Mar 31, 2024" then "Jan 1, 2024"
when T1.'End date' >= "Apr 1, 2024" and T1.'End date' <= "Jun 30, 2024" then "Apr 1, 2024"
when T1.'End date' >= "Jul 1, 2024" and T1.'End date' <= "Sep 30, 2024" then "Jul 1, 2024"
when T1.'End date' >= "Oct 1, 2024" and T1.'End date' <= "Dec 31, 2024" then "Oct 1, 2024"
when T1.'End date' >= "Jan 1, 2025" and T1.'End date' <= "Mar 31, 2025" then "Jan 1, 2025"
when T1.'End date' >= "Apr 1, 2025" and T1.'End date' <= "Jun 30, 2025" then "Apr 1, 2025"
when T1.'End date' >= "Jul 1, 2025" and T1.'End date' <= "Sep 30, 2025" then "Jul 1, 2025"
when T1.'End date' >= "Oct 1, 2025" and T1.'End date' <= "Dec 31, 2025" then "Oct 1, 2025"
else "N/A"
end
as 'Quarter Start',

in the table, the value is displayed as below for all rows.

1672531200000

please help.

thanks

Nagarajan

[Sayed Bares [ServiceRocket]]

@Nagarajan Kalyana Subramanian I believe you'd need to cast your values to Date. 

For example, when T1.'End date' >= "Apr 1, 2023" and T1.'End date' <= "Jun 30, 2023" then CAST("Apr 1, 2023" as DATE)

Hope it helps!

[Nagarajan Kalyana Subramanian]

Hi, thanks for coming back. can i also format the date to M dd,yy format, if so, what syntax to use please?

[Stiltsoft support]

Hi @Nagarajan Kalyana Subramanian ,

Please specify your required format via the FORMATDATE function:

then FORMATDATE("Jan 1, 2023", "M dd, yy")

[Nagarajan Kalyana Subramanian]

thank you, it all helped.