Date Field and Calculated Column

[wnicholl]

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('APPDATP (NB)'[QEFDT],5,2),LEFT('APPDATP (NB)'[QEFDT],2))

Using the above calculated column I'm getting this result 5/20/2006 which is showing the wrong day.  Is there a way to show the corrcet day 5/23/2006?  Thank you in advance!

Here is a sample of the date field. The data type = Text and the Format = Text. 

QEFDT

20060523

20070417

20051201

20231021

[wnicholl · Accepted solution]

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

[wnicholl]

I have the exact formula you provided. Not sure why it's not working for me. Thank you for your help.

Effective Date =

               DATE(LEFT('APPDATP (NB)'[QEFDT],4),MID('APPDATP (NB)'[QEFDT],5,2),RIGHT('APPDATP (NB)'[QEFDT],2))

[Ashish_Mathur]

Hi,

The formula is correct.  In the Query Editor, change the data type to Whole Number.

[wnicholl]

Thanks for reaching out. I changed the data type to Whole Number in query editor and I'm still getting an error. 

[Ashish_Mathur]

I cannot help you unless i see the file.

[wnicholl]

I'm connected to AS400 so their is no external fime available. I can provide a copy of the data is that helps.  Thank you! 

[gadielsolis]

Please check if there's a typo, I just tried in my PC and it's working.

[gadielsolis]

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]

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.