Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

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

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!

1 ACCEPTED SOLUTION
Super User

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 )``````

2 REPLIES 2
Helper II

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

Super User

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 )``````

Helpful resources

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors