Forum Discussion
Solved
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 ...
This issue may be based on your windows settings. My settings are below and my Power BI returns 1993.
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
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!
