topic Re: How to convert number to date, 5 & 6 digits to dd/mm/yyyy in DAX Commands and Tips

How to convert number to date, 5 & 6 digits to dd/mm/yyyy

feralvarez994 — Fri, 08 Apr 2022 20:30:12 GMT

Hi everybody! I'm wondering how do i do to convert a number string to a date string, i have this diferrents formats and i can not find out how to convert them to a date string.

In the first problem, i have text strings with 7 and 8 length, the last 4 digits are for the year, mean the previous 2 mean the month, and the first one is the day. An example is:

- 4112006 = 4/11/2006 (4th of November 2006)

I can do this if there's only 8 digits strings with the date, left, and right function but dont know how to do it with de 7th strings.

Also, i have other columns that refers the date in 5 or 6 digit strings, like this example:

Examples in this columns:

10917 refers to 1th of September 2017 (the 0 refers to the month)
101121 refers to 10th of November 2021.

Hope to be clear, anything else that you need i am here to answer. Thanks everyone!

Re: How to convert number to date, 5 & 6 digits to dd/mm/yyyy

tamerj1 — Fri, 08 Apr 2022 22:05:51 GMT

Hi feralvarez994

You try

AT_FECHAS_1_NEW = VAR Length = LEN ( Table[AT_FECHAS_10] ) VAR Modified = IF ( Length = 7, "0" & Table[AT_FECHAS_10], Table[AT_FECHAS_10] ) VAR TheYear = VALUE ( RIGHT ( Modified, 4 ) ) VAR TheMonth = VALUE ( MID ( Modified, 3, 2 ) ) VAR TheDay = VALUE ( LEFT ( Modified, 2 ) ) RETURN DATE ( TheYear, TheMonth, TheDay )

AT_FEC_A_VENTA_NEW = VAR Length = LEN ( Table[AT_FEC_A_VENTA] ) VAR Modified = IF ( Length = 5, "0" & Table[AT_FEC_A_VENTA], Table[AT_FEC_A_VENTA] ) VAR TheYear = VALUE ( "20" & RIGHT ( Modified, 2 ) ) VAR TheMonth = VALUE ( MID ( Modified, 3, 2 ) ) VAR TheDay = VALUE ( LEFT ( Modified, 2 ) ) RETURN DATE ( TheYear, TheMonth, TheDay )

Re: How to convert number to date, 5 & 6 digits to dd/mm/yyyy

feralvarez994 — Mon, 11 Apr 2022 15:30:07 GMT

Hi tamerj1, it works! Thanks you a lot, i accept it as solution