Change Date format from Text to Date

[DS14]

Hi Experts,

I have to change the format of year column from text to date  format. I have the Year data in following format.

Fixed -Pay %	Varaible -Pay %	Pers.No.	Year
0	0	10000398	2019-20
0	0	10000442	2019-20
0	0	10000447	2019-20
0	0	10000454	2019-20
0	0	10000477	2019-20
0.001110487	0.196100604	10000001	2019-20
0.00647306	0.230132449	10000022	2019-20
0.007262928	0.188228022	10000057	2019-20
0.008715513	0.281253869	10000133	2019-20
0.010282058	0.327032225	10000039	2019-20
0.01116693	0.10854293	10000446	2019-20

I need the output year column as date format and in year column as date format  2019-2020.

Please suggest .

[Ivan_Bozov]

Hello! How about this:

DATE(DATE#(LEFT(Year,4),'YYYY'),'YYYY') & '-' & DATE(DATE#(RIGHT(Year,2),'YY'),'YYYY') AS Year

[DS14]

& '-' & using this the format is still in text. It's not in number format.

[Ivan_Bozov]

You can of course separate that

DATE(DATE#(LEFT(Year,4),'YYYY'),'YYYY') AS Year1,
DATE(DATE#(RIGHT(Year,2),'YY'),'YYYY') AS Year2

and then use the following as dimension in your chart

Year1 & '-' & Year2

[tresesco]

Your one year field value is '2019-20' and you want this value to be in date format ? Could you explain a bit more. Usually in such financial year representation it is recommended to use dual(), may be something like:

Dual(YearField, Left(YearField,4)) as Year 

This will give you value (text at at the UI) '2019-20' and a numeric value 2019 at the underlying value you can perform mathematical operation on.