Text won't turn to Date

[Anonymous]

Hi, 

I have tried converting my text to date by changing the Data Type formatting in the Column tools but just receive an error. See pic below. 

I also tried to create a column with the following code and received an error.

Date = FORMAT(LEFT(BOOJ30MF[MMDD],2) & RIGHT(BOOJ30MF[MMDD],2) & LEFT(BOOJ30MF[BOOJRSDT],4),"MM/DD/YYYY")

[mhossain]

@Anonymous 

If you want to take DAX approach: You need to use Date function and mentione year, month day.

DATE(

RIGHT([DateText],4),

LEFT([BOOJ30MF[Date],2),

MID([DateTest],3,2))

You can do in powerquery too, using Date.FromText

[Anonymous]

Hi @Anonymous 

Use

FORMAT(table[col],"MMDDYYYY")

[Anonymous]

@ pranit828 - This still gives me an error 😕

[Anonymous]

Hi @Anonymous 

What is the data type of [date] column?

you can use the below function instead.

DATE(RIGHT(BOOJ30MF[Date],4),LEFT(BOOJ30MF[Date],2),MID(BOOJ30MF[Date],3,2))

[MFelix]

HI @Anonymous .

Try this formula:

Date = Date(LEFT(BOOJ30MF[BOOJRSDT],4),LEFT(BOOJ30MF[MMDD],2) ,RIGHT(BOOJ30MF[MMDD],2))

[Anonymous]

Hi @MFelix , 

I am receiving the error "An argument of the function 'DATE' has the wrong data type or the result is too large or too small."

[MFelix]

Hi @Anonymous ,

I have test it out and everything is working correcty can you confirm the formula you are using please.

[Anonymous]

@MFelix @Anonymous 

Using the Date function isn't working. 

[Anonymous]

HI @Anonymous 

One or both columns from which you are pulling the year, month and date is of text type.

Convert it to number and use the same formulate as in your screen shot

or use,

DATE(FORMAT(LEFT(BOOJ30MF[Date],4),"####"),FORMAT(LEFT(BOOJ30MF[MMDD],2),"##"),FORMAT(RIGHT(BOOJ30MF[MMDD],2),"##"))

[Anonymous]

@Anonymous 

Still receiving the error 😕

[Anonymous]

Hi @Anonymous 

Did you change the data type of both columns to number? looks like it is still text.

Also, Create this as a measure.

[Anonymous]

@Anonymous 

Ok I changed both columns to "whole number" then tried to create the measure but it won't find the columns I am trying to pull in.

[Anonymous]

Hi @Anonymous ,

That's because you're creating a measure, please create a calculated column with below formula(just keep the data type of column BOOJRSDT and MMDD as Text) and set the date format for it in Column Tools ribbon.

Column = DATE(LEFT('BOOJ30MF'[BOOJRSDT],4),LEFT('BOOJ30MF'[MMDD],2),RIGHT('BOOJ30MF'[MMDD],2))

Best Regards

Rena

[Anonymous]

@Anonymous 

I did that and it still is giving me and error

[Anonymous]

I think there is an issue because the date field I am pulling in is coming from a database. I am trying to find a workaround but have been unsuccessful with everything I try. 

[Anonymous · Accepted solution]

I figured it out. I had to create an If statement to get rid of the 0s. Then I created another column with the following equation: 

Column 2 = Date(LEFT(BOOJ30MF[Requested date],4),MID(BOOJ30MF[Requested date],5,2) ,RIGHT(BOOJ30MF[Requested date],2))

[MFelix]

Hi @Anonymous 

That type of information will not give you a date format you need to do the following:

Date = LEFT(BOOJ30MF[MMDD],2) &"/&RIGHT(BOOJ30MF[MMDD],2) & "/" & LEFT(BOOJ30MF[BOOJRSDT],4)

And then format the column as a date column.

[Anonymous]

@ MFelix - I am getting the error "Cannot convert value "0/0/0" of type Text to type Date

[Anand24]

Hi @Anonymous ,

Power BI doesn't recognise MMDDYYYY directly as a Date format. That's why you are not able to directly convert into date field.

Same is the issue when you use FORMAT function

Create a calculated column by splitting and joining existing column to have data in the format of a date(MM/DD/YYYY or DD/MM/YYYY or any other Date format).

Probably this new column will be of text data type but in Power BI recognizable date format, hence you will just need to convert this date data type.

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

[Anand24]

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

[Anonymous]

@Anand24 

I did that but it still is formated as text even though it looks like a date.