Changing data type from text to date results in some values being wrong.

[Aleksis]

Hello,

I'm trying to convert my date which is in text format to the date format, to use for DATEDIFF later on.

I want this date to be the same format for all entries (let's say MM/DD/YYYY or 7 October 2022). However, when I try to change the data type, some of the dates have mm/dd/yyyy and some have dd/mm/yyyy format. Changing the format afterwards keeps the the wrong date.

Original date as text format (MM/DD/YYYY):

Changed into data type: date

As you can see, instead of 10 July 2022, it should be 7 October 2022. On the other hand, some of the values are correctly changed - I assume because obviously month being value 30 is impossible so it gets that correct. Please let me know if you have an idea of how to fix it.

Thanks in advance for your help.

[rsbin]

@Aleksis ,

Although I wasn't able to replicate your issue, I can propose a workaround.  There is a slight complexity because your Day is not consistent and can either be one or two characters. For simplicity, I have developed several columns, but if you want you will be able to combine it all into one:

Month = Left([Due Date], 2)
Year = RIGHT([Due Date], 4 )
Day = IF( LEN([Due Date]) = 10, MID([Due Date],4,2),MID([Due Date],4,1) ) // This is testing if it is one digit or two digit date.
Then finally:
DueDate_Revised = DATE( [Year], [Month], [Day] )

Might not be the cleanest solution, but gets you what you need.

Regards,

[Aleksis]

Hello @rsbin ,

Thanks for your help. However, there is a slight issue with this solution. The number of characters can range from 8 to 10 (12/30/2022 or 12/4/2022 or 2/4/2022). There is no problem when the date is 8 OR 10 characters (formula can be adjusted easily) but I'm not sure what to do with 9 because it can be 5/11/2022 or 11/5/2022 (so either the month is 2 characters or the day is 2 characters). Any ideas on how to resolve this?

[rsbin]

@Aleksis ,

You can modify the Month column to something like this:

Month = IF( "/", LEFT([Due Date], 2 ), LEFT( [Due Date], 1), Left([Due Date],2) 
//Searches for the first "/".  If it finds it, then take only first digit, if not take 2.

You may have to alter the Day Column as well if you find other scenarios I haven't covered.

Here is a great source of info for you:
https://learn.microsoft.com/en-us/dax/text-functions-dax

Regards,

[Aleksis · Accepted solution]

Thank you for the help once again. I played around with the formulas and finally came to the following columns and measures. Since Due Day has 4 possible cases, then I just combined the 3 IF statements.

Due Day = IF( AND(MID([Due Date], 2,1)="/",MID([Due Date], 5,1)= "/"), MID([Due Date],3,2), IF(AND(MID([Due Date],2,1)= "/",MID([Due Date],4,1)= "/"), MID([Due Date],3,1), IF(AND(MID([Due Date],3,1)= "/",MID([Due Date],6,1)= "/"), MID([Due Date],4,2), MID([Due Date],4,1)))

Due Year = RIGHT([Due Date],4)

Due Month = SUBSTITUTE(IF( LEFT([Due Date], 2)="/", LEFT( [Due Date], 1), Left([Due Date],2)),"/","")

The number of days overdue is now showing the correct output with:

Number of days overdue = DATEDIFF([DueDate_Revised],TODAY(),DAY)

where

DueDate_Revised = DATE( [Due Year], [Due Month], [Due Day] )