Conversion from MMM YYYY to date

[SaitoM]

I have an Excel table that contains a column with a date like Jan 2020. It is currently a string. How can I convert it to a real date format, like 01-01-2020? I tried the String to Date&Time node with the format specified as “MMM YYYY” but I just get an error or “?” in every cell.

[elsamuel]

Hi @SaitoM, welcome to the forum.

I have an Excel table that contains a column with a date like Jan 2020. It is currently a string. How can I convert it to a real date format, like 01-01-2020?

Where is the date information going to come from? Do you want each date to be the 1st of a month?

[SaitoM]

Hi, @elsamuel. Yes, I want to make it 1st day of the month.
Thanks

[elsamuel]

Here’s an approach you can play around with:

1. Use the String Manipulation node to create the date in the format ‘dd MMM yyyy’
   join("01 ",$date$)

2. Use the String to Date&Time node to convert this string to a date. Use the ‘dd MMM yyy’ format and ensure that the New Type is Date.

[SaitoM]

Thank you. I was successful to create a new column with the String Manipulation node. It contains data like 01 JAN 2020, but String to Date&Time continues to produce error:
ERROR String to Date&Time 0:73 Execute failed: Failed to parse date in row 'Row0: Text ‘01 JAN 2020’ could not be parsed at index 3
Do you have any idea what I am doing wrong?

[elsamuel]

It sounds like your String to Date&Time node isn’t configured properly.

Can you post a screenshot or upload the workflow an data here?

[SimonS]

Hi @SaitoM,

if you are having “JAN” instead of “Jan” in the String, it won’t work. Add a capitalize() in the String Manipulation node, i.e., capitalize(join("01 ",$date$)). Then use these settings in the String To Date&Time node:

Best,
Simon

[SaitoM]

Thank you @elsamuel and @SimonS. It worked!