Changing text to date column not working

[hallang]

Hi,

I have added in a formula to convert a column from DD MMMM YYYY to MMMM YYYY.  I have added a column to do this but when I try and change the data type to 'Date' its saying it cannot convert it -

can anyone help with this?

Thanks

[jgeddes]

Have you tried using the DATEVALUE function?
Learn - DATEVALUE() 

[hallang]

Still getting the same error 😞

[jgeddes]

Month_Year = 
DATEVALUE(
    FORMAT('Absence Data'[End], "MMMM YYYY")
)

Does this get you the result you need? 

[hallang]

Nope still getting the same error 😞

[jgeddes]

Alrighty. What happens when you use...

FORMAT('Absence Data'[End], "MMMM YYYY")

without wrapping it in the DATEVALUE()?

[hallang]

that formula works but when I try and change the format of that to date rather than text I get an error.  Can I not change this column so it behaves like a date column?

[jgeddes]

You can absolutely change the column type to date. Based on the continued reappearance of the error I am thinking that there is/are values in your 'End' column that are not playing nice. 
Your initial screen shot did not contain any data in the 'End' column so I have been making assumptions that the data in the 'End' column was sound. My suggestion at this point would be to review the contents of that column to ensure that there are no anomalies.

[hallang]

so the 'end' is a date field, the only thing I can see in it is it has 'null' values but it needs to as on some rows this will be blank

[jgeddes · Accepted solution]

The null values are the issue. 
So let's try this...

Month_Year = 
DATEVALUE(
    IF(
        ISBLANK([End]),
        BLANK(),
        FORMAT([End], "MMMM YYYY")
    )
)

[hallang]

Yes that worked! Thank you! 🙂