Forum Discussion

hallang's avatar
hallang
Frequent Visitor
2 years ago
Solved

Changing text to date column not working

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 ...
Need Help
  • jgeddes's avatar
    jgeddes
    2 years ago

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

    Month_Year = 
DATEVALUE(
    IF(
        ISBLANK([End]),
        BLANK(),
        FORMAT([End], "MMMM YYYY")
    )
)
jgeddes's avatar
jgeddes
Super User
2 years ago
Month_Year = 
DATEVALUE(
    FORMAT('Absence Data'[End], "MMMM YYYY")
)

Does this get you the result you need? 

hallang's avatar
hallang
Frequent Visitor
2 years ago

Nope still getting the same error 😞

  • jgeddes's avatar
    jgeddes
    Super User
    2 years ago

    Alrighty. What happens when you use...

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

    without wrapping it in the DATEVALUE()?

    • hallang's avatar
      hallang
      Frequent Visitor
      2 years ago

      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's avatar
        jgeddes
        Super User
        2 years ago

        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.