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

[feralvarez994]

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!

[feralvarez994]

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

[tamerj1 · Accepted solution]

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 )