Convert Text to Date

1

Hi Dmitry,

I’m just wondering if there’s a way to convert a date like this “02-AUG-2017” to a numeric format like “20170802”?

I tried format(date([Val Date], ‘dd-MMM-yyyy’),‘yyyyMMdd’), but it didnt work.

Thanks for your help~

2

Hi Lianne,

I tried

format(date('02-AUG-2017', 'dd-MMM-yyyy'),'yyyyMMdd')

and it worked as expected. I suspect that [Val Date] could already be number dates that are shown formatted because the column has a format applied. You can see the format in the sidebar on the left, when you click the column header.

If it’s the case then this should work

format([Val Date],'yyyyMMdd')
3

I checked the format of [Val date] column, and it shows “No format”

Tried both format(date([Val Date], ‘dd-MMM-yyyy’),‘yyyyMMdd’) and format([Val Date],‘yyyyMMdd’), neither of them work. The error message is # Inappropriate argument or #Cannot convert to date.

4

Can you make a screenshot of the [Val Date] column and post it here?

Try also inspecting one of the values – right-click a value in [Val Date] that didn’t convert, then choose Metadata. You should see the Cell Metadata window popping up. See if there are leading or trailing spaces in that value. There should be no extra spaces for the expression to work.

5

Hi Dmitry,

Below is a screenshot:

image.png86×763 1.2 KB

I also checked leading or trailing spaces, there’s none.

6

Oh, so the dates have a 2-digit year, not 4-digit! Then you should use a format string with two ‘y’, not four:

format(date([Val Date], ‘dd-MMM-yy’),‘yyyyMMdd’)