Forum Discussion

Broeselchen's avatar
Broeselchen
Helper III
3 years ago
Solved

Number to date

Ok, I'll try it this https://community.powerbi.com/t5/Desktop/Zahl-in-Datum-%C3%A4ndern-Zellen-mit-0-Werten/m-p/3008604#M1026104 in english:

 

I want to change a number into a date. It only works, if there isn't a 0 in the column (KAHJMT). But, if there is a 0, it's a problem (KAGDAT).

 

Need Help
  • Samarth_18's avatar
    Samarth_18
    3 years ago

    Broeselchen You can replace with day as 01 if its 00. Use below code for it.

    Kagdat_datum = 
var _day = if(int(RIGHT ( [KAGDAT], 2 ))=0,01,RIGHT ( [KAGDAT], 2 ))
return IF (
    [KAGDAT] = 0 || [KAGDAT] = BLANK() ,
    BLANK (),
 DATE ( LEFT ( [KAGDAT], 4 ), MID ( [KAGDAT], 5, 2 ),_day)
)

14 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    3 years ago

    Hi Broeselchen ,

     

    Please replace blank with BLANK() in your code. It should be like below:-

    Kagdat_datum =
IF (
    Customers[KAGDAT] = 0,
    BLANK (),
    DATE ( LEFT ( Customers[KAGDAT], 4 ), MID ( Customers[KAGDAT], 5, 2 ), RIGHT ( Customers[KAGDAT], 2 ) )
)
    • Broeselchen's avatar
      Broeselchen
      Helper III
      3 years ago

      No, sorry:

      "An argument of the "DATE" function is of the wrong data type, or the result is too long or too short."

       

      I tried it "KAGDAT" as number and as text.

      • Samarth_18's avatar
        Samarth_18
        Community Champion
        3 years ago

        Broeselchen Please try this:-

        Kagdat_datum =
IF (
    Customers[KAGDAT] = 0 || Customers[KAGDAT] = BLANK() ,
    BLANK (),
    DATE ( LEFT ( Customers[KAGDAT], 4 ), MID ( Customers[KAGDAT], 5, 2 ), RIGHT ( Customers[KAGDAT], 2 ) )
)