Forum Discussion

wnicholl's avatar
wnicholl
Resolver II
2 years ago
Solved

Date Field and Calculated Column

Hello,   I'm currently using a calculated column to convert this date 20060523 (text field) to 05/23/2006.   My calculated column is Effective Date = DATE(LEFT('APPDATP (NB)'[QEFDT],4),MID('APPD...
Need Help
  • wnicholl's avatar
    wnicholl
    2 years ago

    I was able to resolve the problem using the folloing steps.

    1) Open power query
    2) Create a custom query
    3) Add the following M Code = Text.BeforeDelimiter([Date Field], " ")
    4) Change type to Date/Calender and Rename Field
    5) Close and Save

gadielsolis's avatar
gadielsolis
Resolver III
2 years ago

Hello,

 

You should use Right to get the day, you're currently using left which will get you the first 2 numbers of the year, the correct formula should be:
Effective Date = DATE(LEFT('APPDATP (NB)'[QEFDT],4),MID('APPDATP (NB)'[QEFDT],5,2),RIGHT('APPDATP (NB)'[QEFDT],2))

  • wnicholl's avatar
    wnicholl
    Resolver II
    2 years ago
    Effective Date = DATE(LEFT('APPDATP (NB)'[QEFDT],4),MID('APPDATP (NB)'[QEFDT],5,2),RIGHT('APPDATP (NB)'[QEFDT],2))
     
    This gives an error "An argumnet of function 'DATE' has the wrong data type or the result is too large or too small.