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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Connor888
Frequent Visitor

Problems converting dates from text.

Hello! 

 

I have a big long list of dates in dd/mm/yy format I need to convert from text to date so I can perform comparisons, however since a lot of these are pre-2000 I've noticed an issue where these are converted incorrectly. E.g. 12/11/93 (referring to 1993) becomes 12/11/2093

 

Is there an easy way to switch the assumptions? The dates will always be in the past so if so that's easier than splitting and re-combining the date or anything else I can think of.

 

Thanks!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

This issue may be based on your windows settings. My settings are below and my Power BI returns 1993.

 

DateTime.PNG

 

 

 

View solution in original post

6 REPLIES 6
dax
Community Support
Community Support

Hi Connor888, 

Yes, you could refer to above suggestions to change your system date setting. Or you could try to change type to local like below to seewhetehr it work or not.

515.PNG

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

This issue may be based on your windows settings. My settings are below and my Power BI returns 1993.

 

DateTime.PNG

 

 

 

Anonymous
Not applicable

Now that I have thought about the issue some more, you probably do not want to go to every user's machine to ensure that their settings match what you need.  The formula below would work regardless of the user's settings.

 

Date.From(Text.Insert([Date],6,if Text.End([Date],2) < "30" then "20" else "19"),"en-GB")

az38
Community Champion
Community Champion

Hi @Connor888 

Not beautiful, but common solution. add custom column

#date(if Number.FromText(Text.Middle([ShortDate],6,2)) < 30 then Number.FromText(Text.Middle([ShortDate],6,2)) + 2000 else Number.FromText(Text.Middle([ShortDate],6,2))+1900,Number.FromText(Text.Middle([ShortDate],3,2)),Number.FromText(Text.Middle([ShortDate],0,2)))

Pay attention to 30 - each year after 2030 will be calculated as 1930..

it depends on your business logic

 

do not hesitate to kudo useful posts and mark solutions as solution
Linkedin

 

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Hi @az38 

 

Yeah that works. The 30 issue shouldn't matter since all my dates are in the past. A future proof solution might be to take the last two digits of the current year for the comparison, but unless I'm still using this code in 10 years that's probably excessive!

 

Thanks!

az38
Community Champion
Community Champion

Hi @Connor888 

Im glad to help you, good luck!

so, please, mark post with solution as solution for future usage


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors