Forum Discussion

Aleksis's avatar
Aleksis
Regular Visitor
3 years ago
Solved

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

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 O...
Need Help
  • Aleksis's avatar
    Aleksis
    3 years ago

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

     

Aleksis's avatar
Aleksis
Regular Visitor
3 years ago

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's avatar
rsbin
Icon for Community Champion rankCommunity Champion
3 years ago

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's avatar
    Aleksis
    Regular Visitor
    3 years ago

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