Forum Discussion
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('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 |
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
9 Replies
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))- 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.
Please check if there's a typo, I just tried in my PC and it's working.
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))Hi,
The formula is correct. In the Query Editor, change the data type to Whole Number.
Thanks for reaching out. I changed the data type to Whole Number in query editor and I'm still getting an error.
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
