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.
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?
Hi, @elsamuel. Yes, I want to make it 1st day of the month.
Here’s an approach you can play around with:
Use the String Manipulation node to create the date in the format ‘dd MMM yyyy’
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.
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?
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?
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:
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.